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

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