MIME-Version: 1.0
From: Michael Harris <harmic@gmail.com>
Date: Wed, 7 Aug 2024 14:13:10 +1000
Message-ID: <CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@mail.gmail.com>
Subject: ANALYZE on partitioned tables vs on individual partitions
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000800cfa061f10214a"
Archived-At: <https://www.postgresql.org/message-id/CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA%40mail.gmail.com>
Precedence: bulk

--000000000000800cfa061f10214a
Content-Type: text/plain; charset="UTF-8"

Hello Experts,

Our application has a database with a large number of partitioned tables
used to store time series data.

It is partitioned by time: new data is populated into the current
partition, old partitions eventually get dropped, and the partitions in
between are largely static.

I had not realized until recently that the partitioned tables themselves
are not analyzed by the autovacuum processes. I am now wondering if we
should be manually analyzing those.

The problem is they are quite enormous and take a long time to analyze - I
tested one yesterday and it took ~30mins, and we have hundreds of them. It
might not be practical to regularly analyze them.

My questions are:

  1. What is the consequence of not having good statistics on partitioned
table level, if you do have good statistics on the partition level?

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

My reason for asking is I'm trying to understand the tradeoff between the
IO cost associated with frequently analyzing as opposed to possibly
non-optimal plans being chosen.

Thanks in advance, and sorry if this has all been covered before. I could
not find anything definitive in the docs, and while I did find a few
references by googling, none really explained the answers to those
questions too well.

Regards
Mike

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

<div dir=3D"ltr">Hello Experts,<div><br></div><div>Our application has a da=
tabase with a large number of partitioned tables used to store time series =
data.</div><div><br></div><div>It is partitioned by time: new data is popul=
ated into the current partition, old partitions eventually get dropped, and=
 the partitions in between are largely static.</div><div><br></div><div>I h=
ad not realized until recently that the partitioned=C2=A0tables themselves =
are not analyzed by the autovacuum processes. I am now wondering if we shou=
ld be manually analyzing those.</div><div><br></div><div>The problem is the=
y are quite enormous=C2=A0and take a long time to analyze - I tested one ye=
sterday=C2=A0and it took ~30mins, and we have hundreds=C2=A0of them. It mig=
ht not be practical to regularly analyze them.</div><div><br></div><div>My =
questions are:=C2=A0</div><div><br></div><div>=C2=A0 1. What is the consequ=
ence of not having good statistics on partitioned table level, if you do ha=
ve good statistics on the partition=C2=A0level?</div><div><br></div><div>=
=C2=A0 2. Which planning operations rely on partitioned table level statist=
ics?</div><div><br></div><div>My reason for asking is I&#39;m trying to und=
erstand the tradeoff between the IO cost associated with frequently analyzi=
ng as opposed to possibly non-optimal plans being chosen.</div><div><br></d=
iv><div>Thanks in advance, and sorry if this has all been covered before. I=
 could not find anything definitive in the docs, and while I did find a few=
 references by googling, none really explained the answers to those questio=
ns too well.</div><div><br></div><div>Regards</div><div>Mike</div><div><br>=
</div></div>

--000000000000800cfa061f10214a--