| From: | Gabriel Sánchez <gabrielesanchez(at)gmail(dot)com> |
|---|---|
| To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Speeding up ANALYZE on large partitioned tables |
| Date: | 2026-04-01 15:04:43 |
| Message-ID: | CANHuRqGh4x4OAXwePkjhcN2QqCPFHoKxP7astnOUQRB8P0Eyag@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Greg Hennessy | 2026-04-01 15:05:20 | Re: How to query with more workers on a large table with many partitions |
| Previous Message | Gabriel Sánchez | 2026-04-01 14:56:20 | How to query with more workers on a large table with many partitions |