Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: ANALYZE on partitioned tables vs on individual partitions
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@mail.gmail.com>
Date: Tue, 6 Aug 2024 21:43:35 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <D8A15A79-9FCF-4855-AB1E-BD6088065CCC@thebuild.com>
References: <CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@mail.gmail.com>
To: Michael Harris <harmic@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/D8A15A79-9FCF-4855-AB1E-BD6088065CCC%40thebuild.com>
Precedence: bulk


> On Aug 6, 2024, at 21:13, Michael Harris <harmic@gmail.com> wrote:
>=20
>   1. What is the consequence of not having good statistics on =
partitioned table level, if you do have good statistics on the partition =
level?

The child partitions are just tables, and all of the same issues that =
can arise from bad statistics on a table can arise on them: =
specifically, bad query plans.  (There are also some consequences to =
leaving a table unvacuumed, even an append-only table.)  The root table =
in declarative partitioning has no rows, so there really is no such =
thing as "statistics at the partition level."

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

Just about every query uses the information that ANALYZE gathers in some =
way.  If the query can't be executed in any other conceivable way than a =
sequential scan, or if it is a simple lookup on a unique column, the =
statistics will not contribute to the plan, but essentially every other =
plan will use them in one way or another.

Child partitions should be autovacuumed and autoanalyzed just like any =
other table; they are not prohibited from autovacuum in any way by =
default.  It's probably a good idea to investigate why they are not =
being picked up by autovacuum.  If they are created by a bulk load =
process, it's not a bad idea to do a VACUUM ANALYZE on them once the =
bulk load is complete.=