Date: Sun, 10 Jul 2022 23:05:28 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nathan Ward <lists+postgresql@daork.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Occasional performance issue after changing table partitions
Message-ID: <20220711040527.GQ13040@telsasoft.com>
References: <24A451C3-1F45-48ED-B747-4BA17C249AF1@daork.net>
 <20220710140519.GN13040@telsasoft.com>
 <4954694D-9E02-4209-815B-07903F57779A@daork.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <4954694D-9E02-4209-815B-07903F57779A@daork.net>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220711040527.GQ13040%40telsasoft.com>
Precedence: bulk

On Mon, Jul 11, 2022 at 03:21:38PM +1200, Nathan Ward wrote:
> > Note that postgres doesn't automatically analyze parent tables, so you should
> > maybe do that whenever the data changes enough for it to matter.
> 
> Hmm. This raises some stuff I’m not familiar with - does analysing a parent table do anything?

Yes

You could check if you have stats now (maybe due to a global ANALYZE or
analyzedb) and how the query plans change if you analyze.
The transaction may be overly conservative.

SELECT COUNT(1) FROM pg_stats WHERE tablename=PARENT;
SELECT last_analyze, last_autoanalyze, relname FROM pg_stat_all_tables WHERE relname=PARENT;
begin;
SET default_statistics_target=10;
ANALYZE;
explain SELECT [...];
rollback;

> I got the impression that analysing the parent was just shorthand for analysing all of the attached partitions.

Could you let us know if the documentation left that impression ?

See here (this was updated recently).

https://www.postgresql.org/docs/13/sql-analyze.html#id-1.9.3.46.8

For partitioned tables, ANALYZE gathers statistics by sampling rows from all partitions; in addition, it will recurse into each partition and update its statistics. Each leaf partition is analyzed only once, even with multi-level partitioning. No statistics are collected for only the parent table (without data from its partitions), because with partitioning it's guaranteed to be empty.

By contrast, if the table being analyzed has inheritance children, ANALYZE gathers two sets of statistics: one on the rows of the parent table only, and a second including rows of both the parent table and all of its children. This second set of statistics is needed when planning queries that process the inheritance tree as a whole. The child tables themselves are not individually analyzed in this case.

The autovacuum daemon does not process partitioned tables, nor does it process inheritance parents if only the children are ever modified. It is usually necessary to periodically run a manual ANALYZE to keep the statistics of the table hierarchy up to date.

> Perhaps because I attach a table with data, the parent sometimes decides it needs to run analyse on a bunch of things?

No, that doesn't happen.

> Or, maybe it uses the most recently attached partition, with bad statistics, to plan queries that only touch other partitions?

This is closer to what I was talking about.

To be clear, you are using relkind=p partitions (added in v10), and not
inheritance parents, right ?

-- 
Justin