Speeding up ANALYZE on large partitioned tables

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

Browse pgsql-general by date

  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