MIME-Version: 1.0
References: <CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@mail.gmail.com>
 <D8A15A79-9FCF-4855-AB1E-BD6088065CCC@thebuild.com>
In-Reply-To: <D8A15A79-9FCF-4855-AB1E-BD6088065CCC@thebuild.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 7 Aug 2024 17:22:51 +1200
Message-ID: <CAApHDvrjTL3qo70C1snX7yLQwPpWoANL6sigcD6OTWqy7Y3+Nw@mail.gmail.com>
Subject: Re: ANALYZE on partitioned tables vs on individual partitions
To: Michael Harris <harmic@gmail.com>
Cc: pgsql-general@lists.postgresql.org, Christophe Pettus <xof@thebuild.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrjTL3qo70C1snX7yLQwPpWoANL6sigcD6OTWqy7Y3%2BNw%40mail.gmail.com>
Precedence: bulk

On Wed, 7 Aug 2024 at 16:44, Christophe Pettus <xof@thebuild.com> wrote:
> Child partitions should be autovacuumed and autoanalyzed just like any ot=
her 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 b=
y autovacuum.  If they are created by a bulk load process, it's not a bad i=
dea to do a VACUUM ANALYZE on them once the bulk load is complete.

I think the complaint was about no autovacuum on the partitioned
table, not the partitions.  This is expected as we don't track the
counters (in particular n_mod_since_analyze) shown in
pg_stat_all_tables at the partitioned table level, so the trigger
points that normally cause autovacuum to analyze or vacuum a table
just won't be triggered for a partitioned table.  For VACUUM, that's
fine as, as you mentioned, no rows are stored. But for analyze, that
does present a problem.

To name the aspects of planning that rely on statistics of the
partitioned table, basically anything above the Append or MergeAppend
which joins the partitioned results together. So that doesn't include
the scans of each partition and any quals that are pushed down to the
scan level as those are able to use the partition level statistics.
However, it does include things like joins, group by, distinct as
those require n_distinct estimates for the partitioned table. It's not
all bad though as the row estimates for each individual partition will
be totalled up through the Append / MergeAppend simply by adding up
the row estimates for each Append / MergeAppend child plan. So, it's
really only an estimation problem for any node that comes after a join
node or a group by node as the output rows for those nodes will depend
on a good n_distinct estimate for the partitioned table.

Partition-wise joins and aggregates do change things a bit as those
features do permit moving those operations below the Append / Merge
Append, in which case the statistics for the individual partition can
be used.

You could consider manually setting the n_distinct_inherited estimates
for the columns that you join on or group by in the partitioned table.
You might find that you're able to choose a suitable value for that if
you review the documentation for that setting. In particular, please
review what is mentioned about using negative numbers for that
setting. You may be able to choose a value that scales correctly with
the row estimate that doesn't get outdated as you add more rows to the
partitions. You'll need to determine that based on the data you're
storing.

David