Re: ANALYZE on partitioned tables vs on individual partitions

> On Aug 6, 2024, at 21:13, Michael Harris <harmic(at)gmail(dot)com> wrote:
>
> 1. What is the consequence of not having good statistics on partitioned table level, if you do have good statistics on the partition level?

The child partitions are just tables, and all of the same issues that can arise from bad statistics on a table can arise on them: specifically, bad query plans. (There are also some consequences to leaving a table unvacuumed, even an append-only table.) The root table in declarative partitioning has no rows, so there really is no such thing as "statistics at the partition level."

> 2. Which planning operations rely on partitioned table level statistics?

Just about every query uses the information that ANALYZE gathers in some way. If the query can't be executed in any other conceivable way than a sequential scan, or if it is a simple lookup on a unique column, the statistics will not contribute to the plan, but essentially every other plan will use them in one way or another.

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.