BUG #17484: Query does not prune partitions correctly - Mailing list pgsql-bugs

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