Hello,
Runtime partition pruning don't work without index on a hash partitioned
column.
Consider this test case on version 12:
create table data (
key_id integer not null,
value real not null
) partition by hash(key_id);
create table data_0 partition of data for values with (modulus 3,
remainder 0);
create table data_1 partition of data for values with (modulus 3,
remainder 1);
create table data_2 partition of data for values with (modulus 3,
remainder 2);
insert into data (key_id, value)
select floor(random() * 100), random()
from generate_series(0, 1000000) as g (i);
vacuum analyze data;
explain (analyze, settings, costs off, timing off, summary off)
with keys(id) as (
values (1),(2)
) select * from data join keys on (data.key_id = keys.id);
QUERY PLAN
----------------------------------------------------------------------------
Gather (actual rows=19845 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (actual rows=6615 loops=3)
Hash Cond: (data_2.key_id = "*VALUES*".column1)
-> Parallel Append (actual rows=333334 loops=3)
-> Parallel Seq Scan on data_2 (actual rows=126670 loops=3)
-> Parallel Seq Scan on data_1 (actual rows=160458 loops=2)
-> Parallel Seq Scan on data_0 (actual rows=299075 loops=1)
-> Hash (actual rows=2 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Values Scan on "*VALUES*" (actual rows=2 loops=3)
We see that all partitions scanned. But after creating index postgres
actually looks only to selected partitions:
create index on data(key_id);
explain (analyze, settings, costs off, timing off, summary off)
with keys(id) as (
values (1),(2)
) select * from data join keys on (data.key_id = keys.id);
QUERY PLAN
-------------------------------------------------------------------------------------
Nested Loop (actual rows=19845 loops=1)
-> Values Scan on "*VALUES*" (actual rows=2 loops=1)
-> Append (actual rows=9922 loops=2)
-> Bitmap Heap Scan on data_0 (actual rows=9926 loops=1)
Recheck Cond: (key_id = "*VALUES*".column1)
Heap Blocks: exact=1324
-> Bitmap Index Scan on data_0_key_id_idx (actual
rows=9926 loops=1)
Index Cond: (key_id = "*VALUES*".column1)
-> Bitmap Heap Scan on data_1 (never executed)
Recheck Cond: (key_id = "*VALUES*".column1)
-> Bitmap Index Scan on data_1_key_id_idx (never executed)
Index Cond: (key_id = "*VALUES*".column1)
-> Bitmap Heap Scan on data_2 (actual rows=9919 loops=1)
Recheck Cond: (key_id = "*VALUES*".column1)
Heap Blocks: exact=1679
-> Bitmap Index Scan on data_2_key_id_idx (actual
rows=9919 loops=1)
Index Cond: (key_id = "*VALUES*".column1)
Why runtime partition pruning needs index? Is it intended behavior?
--
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company