Обсуждение: BUG #18440: Query does not prune partitions correctly or use index when prepared statements are used
BUG #18440: Query does not prune partitions correctly or use index when prepared statements are used
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 18440 Logged by: Damir Ciganović-Janković Email address: damir.ciganovic.jankovic@gmail.com PostgreSQL version: 15.6 Operating system: Linux Description: Note, same bug from https://www.postgresql.org/message-id/17484-716a1fcbcb0e379b%40postgresql.org , I was instructed to check on Postgres 14, but the issue is still present in 15.6 and 16.2 versions. I will repeat the steps for reproducing, but note that we have also experience poor performance when index was not used due to prepared statements. Current workaround is to not use prepared statements Steps to reproduce: -- 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;
Re: BUG #18440: Query does not prune partitions correctly or use index when prepared statements are used
От
"David G. Johnston"
Дата:
On Tue, Apr 16, 2024 at 11:56 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18440
Logged by: Damir Ciganović-Janković
Email address: damir.ciganovic.jankovic@gmail.com
PostgreSQL version: 15.6
Operating system: Linux
Description:
Note, same bug from
https://www.postgresql.org/message-id/17484-716a1fcbcb0e379b%40postgresql.org
, I was instructed to check on Postgres 14, but the issue is still present
in 15.6 and 16.2 versions.
Like with the other report this isn't a bug report. It is a symptom of difficulty in explaining how all these complex features interact with each other and thus users having the wrong expectations.
Compare the plans for a circa v13 execution and a circa v14 execution to see what was gained.
In short, expecting even a custom plan of a prepared statement to match an unprepared statement is incorrect. There is still considerable saved worked done without knowledge of parameters that has to be ignored, not discarded.
David J.