Re: Occasional performance issue after changing table partitions

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