Re: BUG #17484: Query does not prune partitions correctly

Great, thanks for your response, I will check behavior with Postgres 14 to
see if we have the same problems there.

On Wed, May 18, 2022 at 12:54 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 18 May 2022 at 22:02, PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> > -- Analyzes ALL 4 partitions even though we specified that we want only
> > 'FIRST'. Should have analyzed only test_FIRST_1. NOTE: same result with
> > EXPLAIN ANALYZE
> > PREPARE my_prepared_statement(text, text, text) AS UPDATE test SET
> some_id =
> > 5 WHERE timestamp_col >= CAST($1 AS timestamp(6)) AND timestamp_col <
> > CAST($2 AS timestamp(6)) AND enum_col = $3::test_enum;
> > EXPLAIN EXECUTE my_prepared_statement('2022-01-01 01:00:00', '2022-01-01
> > 02:00:00', 'FIRST');
> >
> > -- example when statement is not prepared (works as expected):
> > EXPLAIN UPDATE test SET some_id = 5 WHERE timestamp_col >=
> CAST('2022-01-01
> > 01:00:00' AS timestamp(6)) AND timestamp_col < CAST('2022-01-01
> 02:00:00' AS
> > timestamp(6)) AND enum_col = 'FIRST'::test_enum;
>
> Thanks for mentioning this, but as far as I can see, this behaves as
> expected.
>
> Execution-time pruning only exists for Append and MergeAppend in
> PostgreSQL 13. The documents [1] mention this, per:
>
> "Execution-time partition pruning currently only occurs for the Append
> and MergeAppend node types. It is not yet implemented for the
> ModifyTable node type, but that is likely to be changed in a future
> release of PostgreSQL."
>
> It might not be obvious from the plan, but your query fits into the
> ModifyTable category. That limitation was removed in PostgreSQL 14.
> See the release nodes in [2].
>
> "Improve the performance of updates and deletes on partitioned tables
> with many partitions (Amit Langote, Tom Lane)"
>
> And in particular:
>
> "This change greatly reduces the planner's overhead for such cases,
> and also allows updates/deletes on partitioned tables to use
> execution-time partition pruning."
>
> David
>
> [1] https://www.postgresql.org/docs/13/ddl-partitioning.html
> [2] https://www.postgresql.org/docs/current/release-14.html
>