Ok there are reproducer test case on the stock postgresql 16.4 config:
--good case with small relation
drop table t1;
create table t1 (a int);
insert into t1 select (random()*100)::int from generate_Series(1,1000000)X;
create index on t1(a);
analyze t1;
set min_parallel_index_scan_size to 0;
set min_parallel_table_scan_size to 0;
set parallel_tuple_cost to 0;
set parallel_setup_cost to 0;
set debug_parallel_query to on;
set enable_bitmapscan to 0;
set enable_seqscan to 0;
explain analyze select * from t1 where a<10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.42..19070.70 rows=91047 width=4) (actual
time=0.527..66.086 rows=95306 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Index Only Scan using t1_a_idx on t1
(cost=0.42..19070.70 rows=37936 width=4) (actual time=0.046..19.293
rows=31769 loops=3)
Index Cond: (a < 10)
Heap Fetches: 95306
Planning Time: 0.227 ms
Execution Time: 69.944 ms
--bad case with big relation
--let's make relation 100x larger (and supposedly better suited for
parallel scans)
drop table t1;
create table t1 (a int);
insert into t1 select (random()*10000)::int from generate_Series(1,100000000)X;
create index on t1(a);
analyze t1;
set min_parallel_index_scan_size to 0;
set min_parallel_table_scan_size to 0;
set parallel_tuple_cost to 0;
set parallel_setup_cost to 0;
set debug_parallel_query to on;
set enable_bitmapscan to 0;
set enable_seqscan to 0;
explain analyze select * from t1 where a<10;
explain analyze select * from t1 where a<10;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=0.57..357316.03 rows=98718 width=4) (actual
time=59.419..651.500 rows=95072 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Index Only Scan using t1_a_idx on t1 (cost=0.57..357316.03
rows=98718 width=4) (actual time=0.030..585.517 rows=95072 loops=1)
Index Cond: (a < 10)
Heap Fetches: 95072
Planning Time: 0.144 ms
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.396 ms, Inlining 0.000 ms, Optimization 0.000
ms, Emission 0.000 ms, Total 0.396 ms
Execution Time: 660.359 ms
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
