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

The following bug has been logged on the website:

Bug reference:      17484
Logged by:          Damir Ciganović-Janković
Email address:      damir.ciganovic.jankovic@gmail.com
PostgreSQL version: 13.5
Operating system:   Windows 10, CentOS 7
Description:

We have upgraded Postgres from 11.12 to 13.5 and have started to
experience
some problems with one query. We are using Java and postgres jdbc driver, 
but we have managed to reproduce the problem with psql.

-- Starting Schema.
DROP TABLE IF EXISTS test;
DROP TYPE IF EXISTS test_enum;
DEALLOCATE my_prepared_statement;
DEALLOCATE my_prepared_statement_with_enum_inlined;
DEALLOCATE my_prepared_statement_multi_partition;
DROP FUNCTION IF EXISTS create_test_partitions;
 
CREATE TYPE test_enum AS ENUM ('FIRST', 'SECOND');
-- NOTE: Partitions are by range with 2 values, one is enum, other is
timestamp
CREATE TABLE test (enum_col test_enum, timestamp_col timestamp, some_id int,
status int) PARTITION BY RANGE (enum_col, timestamp_col);
 
-- Case: Wrong partition pruning
-- Create 4 partitions of test table:
CREATE TABLE test_FIRST_1 PARTITION OF test FOR VALUES FROM ('FIRST',
'2022-01-01') TO ('FIRST', '2022-01-02');
CREATE TABLE test_FIRST_2 PARTITION OF test FOR VALUES FROM ('FIRST',
'2022-01-02') TO ('FIRST', '2022-01-03');
 
CREATE TABLE test_SECOND_1 PARTITION OF test FOR VALUES FROM ('SECOND',
'2022-01-01') TO ('SECOND', '2022-01-02');
CREATE TABLE test_SECOND_2 PARTITION OF test FOR VALUES FROM ('SECOND',
'2022-01-02') TO ('SECOND', '2022-01-03');


-- 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;


-- Analyzes both test_FIRST_1 and test_FIRST_2 despite looking for only
'2022-01-01'. NOTE: same result with EXPLAIN ANALYZE
PREPARE my_prepared_statement_with_enum_inlined(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 = 'FIRST';
EXPLAIN EXECUTE my_prepared_statement_with_enum_inlined('2022-01-01
01:00:00', '2022-01-01 02:00:00');

-- 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;

This causes our query to be really slow. What I noticed is that this also
might have
happened on 11.12, but we didn't notice this until second problem started
to
appear, but lets not bother about that one.

MORE BACKGROUND: 
1. While my query here looks like "With X as (SELECT) -> UPDATE ", my
original query looks like "Create unlogged table AS (With X as (SELECT),
with Y as UPDATE returning * -> SELECT FROM Y" which basically means that
I
am updating table and creating new table with updated records. I have
tried
to simplify it to easier explain the problem here, hopefully I succeeded.
2. Current workaround is to not use prepared statement inside application
that is executing this problematic query, but we would prefer to use them
if
this would be fixed.

Thank you in advance, Damir

On Wed, 18 May 2022 at 22:02, PG Bug reporting form
<noreply@postgresql.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