I accidently encountered a case where gather merge was picked as
default but disabling same by setting max_parallel_workers_per_gather
= 0; produced a non-parallel plan which was faster than gather merge,
but its cost is marked too high when compared to gather merge.
I guess we need some cost adjustment is planner code.
Test setting
=========
create table test as (select id, (random()*10000)::int as v1, random() as
v2 from generate_series(1,1000000) id);
create index test_v1_idx on test (v1);
Server setting is default.
postgres=# explain analyze select * from test order by v1, v2 limit 10;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=19576.71..19577.88 rows=10 width=16) (actual
time=265.989..265.995 rows=10 loops=1) -> Gather Merge (cost=19576.71..116805.80 rows=833334 width=16)
(actual time=265.987..265.992 rows=10 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort
(cost=18576.69..19618.36rows=416667 width=16)
(actual time=250.202..250.424 rows=911 loops=3) Sort Key: v1, v2 Sort Method: external merge
Disk:9272kB -> Parallel Seq Scan on test (cost=0.00..9572.67
rows=416667 width=16) (actual time=0.053..41.397 rows=333333 loops=3)Planning time: 0.193 msExecution time: 271.222 ms
postgres=# set max_parallel_workers_per_gather = 0;
SET
postgres=# explain analyze select * from test order by v1, v2 limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------Limit
(cost=37015.64..37015.67 rows=10 width=16) (actual
time=211.582..211.584 rows=10 loops=1) -> Sort (cost=37015.64..39515.64 rows=1000000 width=16) (actual
time=211.581..211.582 rows=10 loops=1) Sort Key: v1, v2 Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on test (cost=0.00..15406.00 rows=1000000
width=16) (actual time=0.085..107.522 rows=1000000 loops=1)Planning time: 0.093 msExecution time: 211.608 ms
(7 rows)
--
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com
