From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: stanislaw.skonieczny@gmail.com
Subject: BUG #17270: Partitions are not prunned for generic plans when `ANY` construct is used.
Date: Wed, 03 Nov 2021 11:48:12 +0000
Message-ID: <17270-3e62104d1566e7c0@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, stanislaw.skonieczny@gmail.com
Subject: Re: BUG #17270: Partitions are not prunned for generic plans when `ANY` construct is used.
In-Reply-To: <17270-3e62104d1566e7c0@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox