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
>

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.

--
Maxim Boguk
Senior Postgresql DBA

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