MIME-Version: 1.0
From: =?UTF-8?Q?Gabriel_S=C3=A1nchez?= <gabrielesanchez@gmail.com>
Date: Wed, 1 Apr 2026 11:04:43 -0400
Message-ID: <CANHuRqGh4x4OAXwePkjhcN2QqCPFHoKxP7astnOUQRB8P0Eyag@mail.gmail.com>
Subject: Speeding up ANALYZE on large partitioned tables
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000006091d064e6768be"
Archived-At: <https://www.postgresql.org/message-id/CANHuRqGh4x4OAXwePkjhcN2QqCPFHoKxP7astnOUQRB8P0Eyag%40mail.gmail.com>
Precedence: bulk

--00000000000006091d064e6768be
Content-Type: text/plain; charset="UTF-8"

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

--00000000000006091d064e6768be
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi PostgreSQL community,</div><div><br></div><div>I h=
ave a database=C2=A0with several very large tables (for example, 86 GB) tha=
t are partitioned by year, sub-partitioned by month, and sub-sub-partitione=
d by day. Each day a new partition is added, and that partition is immediat=
ely ANALYZEd by my process.=C2=A0</div><div><br></div><div>However I notice=
d that sometimes the query plans for queries on the top-level partitioned t=
able don&#39;t make much sense, and I read in the documentation that ANALYE=
ing=C2=A0the leaf partitions doesn&#39;t update the statistics of the paren=
t 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 wh=
ile to ANALYZE the 86 GB table with hundreds of sub-sub=3Dpartitions, becau=
se that ANALYZE triggers an ANALYZE on each partition down the tree. Since =
leaf tables are always ANALYZED when created, isn&#39;t there a way to upda=
te the statistics of the parent and grandparent tables based on the statist=
ics already calculated for the partitions? Maybe with an ANALYZE ONLY [top-=
level table]?</div><div><br></div><div>Thanks,</div><div>Gabriel</div><div>=
<br></div></div>

--00000000000006091d064e6768be--