Many thanks David for the comprehensive response. > I think the complaint was about no autovacuum on the partitioned > table, not the partitions. Yes, exactly. One other piece of information: these tables contain a lot of columns, of which only 4 are normally used for WHERE clauses or joins. The table I was experimenting with has 150 columns, 156026832 rows and occupies 166GB. I found that running an ANALYZE specifying only those 4 columns only took 5 minutes, compared to the 30 minutes for the whole table. That was a bit of a surprise as I imagined actually reading the table would take most of the time and would be the same regardless of the number of columns being analyzed, but I guess that is wrong. Regards, Mike On Wed, 7 Aug 2024 at 15:23, David Rowley <dgrowleyml@xxxxxxxxx> wrote: > > On Wed, 7 Aug 2024 at 16:44, Christophe Pettus <xof@xxxxxxxxxxxx> wrote: > > Child partitions should be autovacuumed and autoanalyzed just like any other table; they are not prohibited from autovacuum in any way by default. It's probably a good idea to investigate why they are not being picked up by autovacuum. If they are created by a bulk load process, it's not a bad idea to do a VACUUM ANALYZE on them once the bulk load is complete. > > I think the complaint was about no autovacuum on the partitioned > table, not the partitions. This is expected as we don't track the > counters (in particular n_mod_since_analyze) shown in > pg_stat_all_tables at the partitioned table level, so the trigger > points that normally cause autovacuum to analyze or vacuum a table > just won't be triggered for a partitioned table. For VACUUM, that's > fine as, as you mentioned, no rows are stored. But for analyze, that > does present a problem. > > To name the aspects of planning that rely on statistics of the > partitioned table, basically anything above the Append or MergeAppend > which joins the partitioned results together. So that doesn't include > the scans of each partition and any quals that are pushed down to the > scan level as those are able to use the partition level statistics. > However, it does include things like joins, group by, distinct as > those require n_distinct estimates for the partitioned table. It's not > all bad though as the row estimates for each individual partition will > be totalled up through the Append / MergeAppend simply by adding up > the row estimates for each Append / MergeAppend child plan. So, it's > really only an estimation problem for any node that comes after a join > node or a group by node as the output rows for those nodes will depend > on a good n_distinct estimate for the partitioned table. > > Partition-wise joins and aggregates do change things a bit as those > features do permit moving those operations below the Append / Merge > Append, in which case the statistics for the individual partition can > be used. > > You could consider manually setting the n_distinct_inherited estimates > for the columns that you join on or group by in the partitioned table. > You might find that you're able to choose a suitable value for that if > you review the documentation for that setting. In particular, please > review what is mentioned about using negative numbers for that > setting. You may be able to choose a value that scales correctly with > the row estimate that doesn't get outdated as you add more rows to the > partitions. You'll need to determine that based on the data you're > storing. > > David
