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

The following bug has been logged on the website:

Bug reference:      18588
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 15.6
Operating system:   Ubuntu
Description:

Hi,

I found very strange case when database simple do not use parallel plan in
trivial query (where it should provide huge benefit)
In the same time - if I add one not exists (subselect) the database execute
query in parallel mode (providing 8x speedup).

Initial query:
explain analyze select topic_id as y0_ from
public.negotiation_topic_archive_p005 this_ where
this_.employer_id='816144';

                      QUERY PLAN
                                                       

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using negotiation_topic_archive_p005_employer_id_resume_id_idx
on negotiation_topic_archive_p005 this_  (cost=0.11..2501972.13 rows=505757
width=8) (actual time=0.020..699.322 rows=510040 loops=1)
   Index Cond: (employer_id = 816144)
 Planning Time: 0.132 ms
 Execution Time: 717.005 ms

setting 
set min_parallel_index_scan_size to '8kB';
                                         
set min_parallel_table_scan_size to '8kB';
set parallel_tuple_cost to 0;
set parallel_setup_cost to 0;

have no effect.

Even with the set force_parallel_mode to on - no effect:
negotiation_chat_archive=# explain analyze select topic_id as y0_ from
public.negotiation_topic_archive_p005 this_ where
this_.employer_id='816144';

                         QUERY PLAN
                                                             

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.11..2501972.13 rows=505757 width=8) (actual
time=5.234..748.606 rows=510040 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Index Scan using
negotiation_topic_archive_p005_employer_id_resume_id_idx on
negotiation_topic_archive_p005 this_  (cost=0.11..2501972.13 rows=505757
width=8) (actual time=0.021..662.798 rows=510040 loops=1)
         Index Cond: (employer_id = 816144)
 Planning Time: 0.117 ms
 Execution Time: 768.117 ms


In the same time a bit more complicated query over the same data - happily
work in parallel (added AND NOT EXISTS (SELECT FROM applicant_black_list
WHERE this_.employer_id=applicant_black_list.employer_id AND
this_.applicant_id=applicant_black_list.applicant_id) condition):

explain analyze select topic_id as y0_ from
public.negotiation_topic_archive_p005 this_ where this_.employer_id='816144'
AND NOT EXISTS (SELECT FROM applicant_black_list WHERE
this_.employer_id=applicant_black_list.employer_id AND
this_.applicant_id=applicant_black_list.applicant_id);

                                QUERY PLAN
                                                                          

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=10.27..2498445.60 rows=505754 width=8) (actual
time=1.452..106.335 rows=509901 loops=1)
   Workers Planned: 7
   Workers Launched: 7
   ->  Parallel Hash Anti Join  (cost=10.27..2498445.60 rows=72251 width=8)
(actual time=0.224..94.754 rows=63738 loops=8)
         Hash Cond: ((this_.employer_id = applicant_black_list.employer_id)
AND (this_.applicant_id = applicant_black_list.applicant_id))
         ->  Parallel Index Scan using
negotiation_topic_archive_p005_employer_id_resume_id_idx on
negotiation_topic_archive_p005 this_  (cost=0.11..2497637.07 rows=72251
width=16) (actual time=0.019..85.305 rows=63755 loops=8)
               Index Cond: (employer_id = 816144)
         ->  Parallel Hash  (cost=10.13..10.13 rows=2 width=8) (actual
time=0.027..0.027 rows=5 loops=8)
               Buckets: 1024  Batches: 1  Memory Usage: 40kB
               ->  Parallel Index Only Scan using employer_to_user_index on
applicant_black_list  (cost=0.08..10.13 rows=2 width=8) (actual
time=0.013..0.022 rows=41 loops=1)
                     Index Cond: (employer_id = 816144)
                     Heap Fetches: 5
 Planning Time: 2.550 ms
 Execution Time: 121.810 ms
(14 rows)

I feel something wrong there and I happy to assist debugging this issue (I
can use gdb with separated instance of the same data).
May be I missing some obvious restrictions but cannot find anything such in
documentation and speedup provided by parallel query would be enormous in my
case.

Kind Regards,
Maxim