为什么要经常手工对分区表的主表执行analyze, 更新主表的统计信息.

作者

digoal

日期

2022-03-29

标签

PostgreSQL , 统计信息 , 分区表 , 主表 , 分区 , 叶子分区

分区表是由入口、分支分区、叶子分区组成的分层结构.

数据存储在叶子分区, 入口、分支分区都没有数据, 当数据发生变化时, autovacuum进程触发垃圾回收、自动统计信息收集的动作.

但是, 只有叶子分区有数据, 所以自动统计信息只收集叶子分区的, 不会修改非叶子分区的统计信息. (不知道未来会不会改进这个功能)

而实际使用时, 大量的情况用到的是入口, 即分区表的主表. 如果主表的统计信息更新不及时, 也许会导致很差的执行计划.

怎么办?

经常手工对主表执行analyze, 更新主表的统计信息.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=61fa6ca79b3c566f44831a33bb226f7358ed4511

Document autoanalyze limitations for partitioned tables  
author  Tomas Vondra <tomas.vondra@postgresql.org>    
Mon, 28 Mar 2022 12:27:36 +0000 (14:27 +0200)  
committer   Tomas Vondra <tomas.vondra@postgresql.org>    
Mon, 28 Mar 2022 12:30:00 +0000 (14:30 +0200)  
commit  61fa6ca79b3c566f44831a33bb226f7358ed4511  
tree    1f931e4dbe0cdd54ad0bc75ef3afb79221245c18    tree  
parent  e26114c817b610424010cfbe91a743f591246ff1    commit | diff  
Document autoanalyze limitations for partitioned tables  
When dealing with partitioned tables, counters for partitioned tables  
are not updated when modifying child tables. This means autoanalyze may  
not update optimizer statistics for the parent relations, which can  
result in poor plans for some queries.  
It's worth documenting this limitation, so that people are aware of it  
and can take steps to mitigate it (e.g. by setting up a script executing  
ANALYZE regularly).  
Backpatch to v10. Older branches are affected too, of couse, but we no  
longer maintain those.  
Author: Justin Pryzby  
Reviewed-by: Zhihong Yu, Tomas Vondra  
Backpatch-through: 10  
Discussion: https://postgr.es/m/20210913035409.GA10647%40telsasoft.com  

+  <para>  
+    The autovacuum daemon does not process partitioned tables, nor does it  
+    process inheritance parents if only the children are ever modified.  
+    It is usually necessary to periodically run a manual  
+    <command>ANALYZE</command> to keep the statistics of the table hierarchy  
+    up to date.  
+  </para>  
+   <para>  
+    Tuples changed in partitions and inheritance children do not trigger  
+    analyze on the parent table.  If the parent table is empty or rarely  
+    changed, it may never be processed by autovacuum, and the statistics for  
+    the inheritance tree as a whole won't be collected. It is necessary to  
+    run <command>ANALYZE</command> on the parent table manually in order to  
+    keep the statistics up to date.  
+   </para>  
+   <tip>  
+    <para>  
+     The autovacuum daemon does not issue <command>ANALYZE</command> commands  
+     for partitioned tables.  Inheritance parents will only be analyzed if the  
+     parent itself is changed - changes to child tables do not trigger  
+     autoanalyze on the parent table.  If your queries require statistics on  
+     parent tables for proper planning, it is necessary to periodically run  
+     a manual <command>ANALYZE</command> on those tables to keep the statistics  
+     up to date.  
+    </para>  
+   </tip>  
+   <para>  
+    Partitioned tables are not processed by autovacuum.  Statistics  
+    should be collected by running a manual <command>ANALYZE</command> when it is  
+    first populated, and again whenever the distribution of data in its  
+    partitions changes significantly.  
+   </para>  

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.