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
Date: Tue, 12 Jul 2022 10:43:10 -0500
Message-ID: <20220712154310.GA13040@telsasoft.com> (raw)
In-Reply-To: <84101021-8B67-45AD-83F2-A3C8F0AA4BEE@daork.net>
References: <24A451C3-1F45-48ED-B747-4BA17C249AF1@daork.net>
<20220710140519.GN13040@telsasoft.com>
<4954694D-9E02-4209-815B-07903F57779A@daork.net>
<20220711040527.GQ13040@telsasoft.com>
<E7E17E09-3419-45B1-BFC0-B91D0AB15A07@daork.net>
<84101021-8B67-45AD-83F2-A3C8F0AA4BEE@daork.net>
On Wed, Jul 13, 2022 at 03:13:46AM +1200, Nathan Ward wrote:
> I have been stepping through the various statements which are different between the two functions, and note that when I do math on a timestamp in a SELECT statement (i.e. _event_timestamp - INTERVAL ‘1 hour’),
> the planner takes 50ms or so - note that the result of the timestamp is used to search the partition key.
> If I declare a function which does the math in advance, stores it in a variable and then runs the SELECT, the planner takes less than 1ms.
> Does this mean it’s calculating the timestamp for each partition, or something like that?
I'm not sure I understand what you're doing - the relevant parts of your
function text and query plan would help here.
Maybe auto_explain.log_nested_statements would be useful ?
Note that "partition pruning" can happen even if you don't have a literal
constant. For example:
|explain(costs off) SELECT * FROM metrics WHERE start_time > now()::timestamp - '1 days'::interval;
| Append
| Subplans Removed: 36
> I see Postgres 14 release notes has information about performance improvements in the planner for updates on tables with "many partitions”. Is 444 partitions “many”?
> My updates are all impacting a single partition only.
It sounds like that'll certainly help you. Another option is to update the
partition directly (which is what we do, to be able to use "ON CONFLICT").
I think with "old partitioning with inheritance", more than a few hundred
partitions was considered unreasonable, and plan-time suffered.
With relkind=p native/declarative partitioning, a few hundred is considered
reasonable, and a few thousand is still considered excessive - even if the
planner time is no issue, you'll still run into problems like "work-mem is
per-node", which works poorly when you might have 10x more nodes.
TBH, this doesn't sound related to your original issue.
--
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, lists+postgresql@daork.net, pgsql-performance@lists.postgresql.org
Subject: Re: Occasional performance issue after changing table partitions
In-Reply-To: <20220712154310.GA13040@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