Re: BUG #18588: Cannot force/let database use parallel execution in simple case. - Mailing list pgsql-bugs

Just some results from sample table copied from production DB:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# set enable_seqscan to off;
SET
test=# set enable_bitmapscan to off;
SET
test=# set jit to off;
SET
test=# select employer_id, count(*) from user_tmp.negotiation_topic_archive_test group by 1 order by 2 desc limit 20;
 employer_id |  count  
-------------+---------
     5516123 | 6213979
        2180 | 2871578
        3529 | 2007333
     9498112 | 1952964
     5120112 | 1475554
       39305 | 1428762
        1740 | 1386494
       78638 | 1318288
     1947314 | 1256758
     1689259 | 1221705
       49357 | 1182208
     9498120 | 1061869
     3036416 | 1044110
          80 |  953178
     1942330 |  952285
        4181 |  921238
        4934 |  903208
        7172 |  825062
       87021 |  805812
        3776 |  773594
(20 rows)

test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=5516123;
                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..20867791.00 rows=6412935 width=8) (actual time=0.094..10117.874 rows=6213979 loops=1)
   Index Cond: (employer_id = 5516123)
 Planning Time: 0.221 ms
 Execution Time: 10278.577 ms
(4 rows)

test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=9498120;
                                                                                             QUERY PLAN                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..3684011.82 rows=1082703 width=8) (actual time=0.165..4350.693 rows=1061869 loops=1)
   Index Cond: (employer_id = 9498120)
 Planning Time: 0.129 ms
 Execution Time: 4385.463 ms
(4 rows)

test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=3776;
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..2341528.08 rows=666279 width=8) (actual time=0.152..3491.172 rows=773594 loops=1)
   Index Cond: (employer_id = 3776)
 Planning Time: 0.127 ms
 Execution Time: 3517.418 ms
(4 rows)

test=# set min_parallel_index_scan_size to 0;
SET
test=# set min_parallel_table_scan_size to 0;
SET
test=# set parallel_tuple_cost to 0;
SET
test=# set parallel_setup_cost to 0;
SET
test=# set debug_parallel_query to on;
SET
test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=5516123;
                                                                                                QUERY PLAN                                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.57..20867791.00 rows=6412935 width=8) (actual time=12.920..8932.146 rows=6213979 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..20867791.00 rows=6412935 width=8) (actual time=0.185..8814.097 rows=6213979 loops=1)
         Index Cond: (employer_id = 5516123)
 Planning Time: 0.128 ms
 Execution Time: 9424.556 ms
(8 rows)

test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=9498120;
                                                                                                QUERY PLAN                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.57..3684011.82 rows=1082703 width=8) (actual time=14.698..3837.904 rows=1061869 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..3684011.82 rows=1082703 width=8) (actual time=0.179..3808.156 rows=1061869 loops=1)
         Index Cond: (employer_id = 9498120)
 Planning Time: 0.130 ms
 Execution Time: 3933.561 ms
(8 rows)

test=# explain analyze  select * from user_tmp.negotiation_topic_archive_test where employer_id=3776;
                                                                                               QUERY PLAN                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.57..2341528.08 rows=666279 width=8) (actual time=14.010..3093.991 rows=773594 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Index Scan using negotiation_topic_archive_test_employer_id_key on negotiation_topic_archive_test  (cost=0.57..2341528.08 rows=666279 width=8) (actual time=0.119..3067.923 rows=773594 loops=1)
         Index Cond: (employer_id = 3776)
 Planning Time: 0.127 ms
 Execution Time: 3163.729 ms
(8 rows)

No amount of *_cost changes - provides any benefit

(not even extreme values such as set random_page_cost to 110; + set seq_page_cost to 100; which I expected should make parallel plan way cheaper with parallelized disk access).

I feel this is the same issue as you described but in case of IO latency bound database (which is most of huge datasets over EBS or local ssd storage) - results seem suboptimal.