BUG #18440: Query does not prune partitions correctly or use index when prepared statements are used

The following bug has been logged on the website:

Bug reference: 18440
Logged by: Damir Ciganović-Janković
Email address: damir(dot)ciganovic(dot)jankovic(at)gmail(dot)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;