Re: BUG #18588: Cannot force/let database use parallel execution in simple case.

On Thu, Aug 22, 2024 at 11:52 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> > With sufficiently large table - I didn't manage find a combinations of
> > settings to force parallel execution (im my real case - the database huge
> > and mostly cold on ssd raid - so parallel execution provides almost
> linear
> > speedup with amount of parallel workers even on 1000 rows... not talking
> > about 10k-10m rows, but I cannot convince the PostgreSQL use parallel
> index
> > scan for 2m returned rows case).
>
> Well, there may be something going on in your real case that isn't
> apparent from this toy example. Perhaps look for a better example?
>
> regards, tom lane
>

On the production DB even in fully cached case performance difference 4х
in parallel vs single process mode

single process 3s
explain (analyze, costs, buffers, timing) select * from
user_tmp.negotiation_topic_archive_test this_ where employer_id = 5516123;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using negotiation_topic_archive_test_employer_id_key on
negotiation_topic_archive_test this_ (cost=0.11..11171373.49 rows=6446264
width=8) (actual time=0.020..2823.467 rows=6213979 loops=1)
Index Cond: (employer_id = 5516123)
Buffers: shared hit=1783337
Planning Time: 0.054 ms
Execution Time: 3069.555 ms

parallel (forced adding unrelated antijoin so with some extra work):
explain (analyze, costs, buffers, timing) select * from
user_tmp.negotiation_topic_archive_test this_ where employer_id = 5516123
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..11123882.70 rows=6446241 width=8) (actual
time=0.835..468.214 rows=6213979 loops=1)
Workers Planned: 8
Workers Launched: 8
Buffers: shared hit=1787471
-> Parallel Hash Anti Join (cost=10.27..11123882.70 rows=805780
width=8) (actual time=0.164..484.239 rows=690442 loops=9)
Hash Cond: ((this_.employer_id = applicant_black_list.employer_id)
AND (this_.applicant_id = applicant_black_list.applicant_id))
Buffers: shared hit=1787471
-> Parallel Index Scan using
negotiation_topic_archive_test_employer_id_key on
negotiation_topic_archive_test this_ (cost=0.11..11114968.68 rows=805783
width=8) (actual time=0.031..352.918 rows=690442 loops=9)
Index Cond: (employer_id = 5516123)
Buffers: shared hit=1787076
-> Parallel Hash (cost=10.13..10.13 rows=2 width=8) (actual
time=0.022..0.023 rows=0 loops=9)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=3
-> 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.009..0.009 rows=0 loops=1)
Index Cond: (employer_id = 5516123)
Heap Fetches: 0
Buffers: shared hit=3
Planning:
Buffers: shared hit=4
Planning Time: 0.164 ms
Execution Time: 711.817 ms

Once there is some IO involved - speed difference is usually close to 8x
and so far I didn't find a way
to force a parallel plan except adding cheap unrelated anti-join.

--
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678