BUG #17270: Partitions are not prunned for generic plans when `ANY` construct is used. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17270
Logged by:          Stanisław Skonieczny
Email address:      stanislaw.skonieczny@gmail.com
PostgreSQL version: 13.4
Operating system:   Linux 5.4.0-89-generic #100~18.04.1-Ubuntu
Description:

We recently had a problem at a customer size with very large BD. Sometimes
queries issued from pl/pgSql were scanning all hash partitions instead of
only one. It looks like pg is not able to do partition pruning when generic
plan is chosen and partition is given using `WHERE partition_key =
ANY(some_array_of_ids)` condition.

We have been able to reproduce it locally using prepared statements and
forcing a generic plan:
```
create table kv(key bigint primary key, value bigint) partition by
hash(key);
CREATE TABLE kv_part_0 PARTITION OF kv FOR VALUES WITH (modulus 3, remainder
0);
CREATE TABLE kv_part_1 PARTITION OF kv FOR VALUES WITH (modulus 3, remainder
1);
CREATE TABLE kv_part_2 PARTITION OF kv FOR VALUES WITH (modulus 3, remainder
2);

insert into kv values (0, 0);
insert into kv values (1, 1);
insert into kv values (2, 2);
insert into kv values (3, 3);
insert into kv values (4, 4);
insert into kv values (5, 5);

prepare query_many(bigint[]) as select * from kv where key = ANY($1);

set plan_cache_mode to force_generic_plan;
explain analyze execute query_many(ARRAY[0]);
```

Output of explain analyze is: 
```
                                                          QUERY PLAN
                                                 

------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=8.32..56.78 rows=30 width=16) (actual time=0.045..0.048
rows=1 loops=1)
   ->  Bitmap Heap Scan on kv_part_0 kv_1  (cost=8.32..18.88 rows=10
width=16) (actual time=0.025..0.026 rows=0 loops=1)
         Recheck Cond: (key = ANY ($1))
         ->  Bitmap Index Scan on kv_part_0_pkey  (cost=0.00..8.32 rows=10
width=0) (actual time=0.019..0.019 rows=0 loops=1)
               Index Cond: (key = ANY ($1))
   ->  Bitmap Heap Scan on kv_part_1 kv_2  (cost=8.32..18.88 rows=10
width=16) (actual time=0.005..0.005 rows=0 loops=1)
         Recheck Cond: (key = ANY ($1))
         ->  Bitmap Index Scan on kv_part_1_pkey  (cost=0.00..8.32 rows=10
width=0) (actual time=0.004..0.004 rows=0 loops=1)
               Index Cond: (key = ANY ($1))
   ->  Bitmap Heap Scan on kv_part_2 kv_3  (cost=8.32..18.88 rows=10
width=16) (actual time=0.014..0.015 rows=1 loops=1)
         Recheck Cond: (key = ANY ($1))
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on kv_part_2_pkey  (cost=0.00..8.32 rows=10
width=0) (actual time=0.008..0.008 rows=1 loops=1)
               Index Cond: (key = ANY ($1))
 Planning Time: 0.282 ms
 Execution Time: 0.115 ms
(16 rows)
```
All partitions were scanned.

However, for custom plan partitions are pruned correctly:
```
set plan_cache_mode to force_custom_plan;
explain analyze execute query_many(ARRAY[0]);

                                                          QUERY PLAN
                                                 

------------------------------------------------------------------------------------------------------------------------------
 Index Scan using kv_part_2_pkey on kv_part_2 kv  (cost=0.03..8.04 rows=1
width=16) (actual time=0.022..0.024 rows=1 loops=1)
   Index Cond: (key = ANY ('{0}'::bigint[]))
 Planning Time: 0.213 ms
 Execution Time: 0.055 ms
(4 rows)

```

It works also when `=` is used instead of `ANY` for generic plan:
```
prepare query_one(bigint) as select * from kv where key = $1;
set plan_cache_mode to force_generic_plan;
explain analyze execute query_one(0);

                                                              QUERY PLAN
                                                         

--------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.03..24.14 rows=3 width=16) (actual time=0.025..0.028 rows=1
loops=1)
   Subplans Removed: 2
   ->  Index Scan using kv_part_2_pkey on kv_part_2 kv_1  (cost=0.03..8.04
rows=1 width=16) (actual time=0.023..0.025 rows=1 loops=1)
         Index Cond: (key = $1)
 Planning Time: 0.244 ms
 Execution Time: 0.072 ms
(6 rows)
```

We have reproduced it on 13.3, 13.4 and 14.0.