Hi PostgreSQL community,
I have a database with several very large tables (for example, 86 GB) that are partitioned by year, sub-partitioned by month, and sub-sub-partitioned by day. Each day a new partition is added, and that partition is immediately ANALYZEd by my process.
However I noticed that sometimes the query plans for queries on the top-level partitioned table don't make much sense, and I read in the documentation that ANALYEing the leaf partitions doesn't update the statistics of the parent and grandparent tables. So I have to run ANALYZE on the top-level table, and when I do that the query plan makes more sense. But it takes quite a while to ANALYZE the 86 GB table with hundreds of sub-sub=partitions, because that ANALYZE triggers an ANALYZE on each partition down the tree. Since leaf tables are always ANALYZED when created, isn't there a way to update the statistics of the parent and grandparent tables based on the statistics already calculated for the partitions? Maybe with an ANALYZE ONLY [top-level table]?
Thanks,
Gabriel
