From: Justin Pryzby <pryzby@telsasoft.com>
To: Kamil Frydel <k.frydel@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partitioned table statistics vs autoanalyze
Date: Thu, 22 Jul 2021 07:15:59 -0500
Message-ID: <20210722121559.GE19620@telsasoft.com> (raw)
In-Reply-To: <0d1b394b-bec9-8a71-a336-44df7078b295@gmail.com>
References: <0d1b394b-bec9-8a71-a336-44df7078b295@gmail.com>
On Thu, Jul 22, 2021 at 01:32:51PM +0200, Kamil Frydel wrote:
> table_1 and table_2 are hash partitioned using volume_id column. Usually we
> make analyze on partitions. We do not make analyze on the partitioned table
> (parent).
> However, if we run 'analyze' on the partitioned table then planner starts
> choosing hash join. As a comparison, the execution using nested loop takes
> about 15 minutes and if it is done using hash join then the query lasts for
> about 1 minute. When running 'analyze' for the partitioned table, postgres
> inserts statistics for the partitioned table into pg_stats (pg_statistics).
> Before that, there are only statistics for partitions. We suspect that this
> is the reason for selecting bad query plan.
> updated, the inheritance statistics will not be up to date unless you run
> ANALYZE manually.
> (https://www.postgresql.org/docs/13/sql-analyze.html)
>
> I would appreciate if anyone could shed some light on the following
> questions:
> 1) Is this above paragraph from docs still valid in PG 13 and does it apply
> to declarative partitioning as well? Is running analyze manually on a
> partitioned table needed to get proper plans for queries on partitioned
> tables? Partitioned table (in the declarative way) is ”virtual” and does not
> keep any data so it seems that there are no statistics that can be gathered
> from the table itself and statistics from partitions should be sufficient.
Up through v13, autoanalyze doesn't collect stats on parent tables (neither
declarative nor inheritence). I agree that this doesn't seem to be well
documented. I think it should also be mentioned here:
https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-STATISTICS
In v14 (which is currently in beta), autoanalyze will process the partitioned
table automatically:
https://www.postgresql.org/docs/14/release-14.html
|Autovacuum now analyzes partitioned tables (Yuzuko Hosoya, Álvaro Herrera)
|Insert, update, and delete tuple counts from partitions are now propagated to their parent tables so autovacuum knows when to process them.
> 2) Why does the planner need these statistics since they seem to be unused
> in the query plan. The query plan uses only partitions, not the partitioned
> table.
The "inherited" stats are used when you SELECT FROM table. The stats for the
individual table would be needed when you SELECT FROM ONLY table (which makes
no sense for a partitioned table).
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, k.frydel@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Partitioned table statistics vs autoanalyze
In-Reply-To: <20210722121559.GE19620@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox