Re: very slow queries when max_parallel_workers_per_gather is higher than zero

Some extra info, which might help, increasing the cost of
the parallel_setup_cost to a value of 4500, Postgres doesn't choose the
parallel query anymore, making the query faster again.

db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# set parallel_setup_cost = 4500;
SET
db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# explain analyze SELECT count(*)
FROM f_ticketupdate_aad5jtwal0ayaax AS f
INNER JOIN
dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z AS d
ON (f.dt_event_id = d.id)
WHERE ( 6171 = d."id_euweek" );

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1508646.93..1508646.94 rows=1 width=8) (actual
time=0.067..0.067 rows=1 loops=1)
-> Nested Loop (cost=1638.24..1508474.08 rows=69140 width=0) (actual
time=0.064..0.064 rows=0 loops=1)
-> Bitmap Heap Scan on
dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z d
(cost=4.34..27.35 rows=7 width=4) (actual time=0.016..0.040 rows=7 loops=1)
Recheck Cond: (6171 = id_euweek)
Heap Blocks: exact=7
-> Bitmap Index Scan on
dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_a_id_euweek_idx
(cost=0.00..4.33 rows=7 width=0) (actual time=0.010..0.010 rows=7 loops=1)
Index Cond: (6171 = id_euweek)
-> Bitmap Heap Scan on f_ticketupdate_aad5jtwal0ayaax f
(cost=1633.90..214617.67 rows=87472 width=4) (actual time=0.002..0.002
rows=0 loops=7)
Recheck Cond: (dt_event_id = d.id)
-> Bitmap Index Scan on
f_ticketupdate_aad5jtwal0ayaax_dt_event_id_idx (cost=0.00..1612.03
rows=87472 width=0) (actual time=0.002..0.002 rows=0 loops=7)
Index Cond: (dt_event_id = d.id)
Planning time: 0.528 ms
Execution time: 0.144 ms

On Mon, 16 Apr 2018 at 19:16 Guilherme Pereira <
guilherme(dot)pereira(at)gooddata(dot)com> wrote:

> Hope it's fine to jump in.
>
> db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# set
> max_parallel_workers_per_gather=0;
> SET
> db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# explain analyze SELECT count(*)
> FROM f_ticketupdate_aad5jtwal0ayaax AS f
> INNER JOIN
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z AS d
> ON (f.dt_event_id = d.id)
> WHERE ( 6171 = d."id_euweek" );
>
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=1508646.93..1508646.94 rows=1 width=8) (actual
> time=0.145..0.145 rows=1 loops=1)
> -> Nested Loop (cost=1638.24..1508474.08 rows=69140 width=0) (actual
> time=0.142..0.142 rows=0 loops=1)
> -> Bitmap Heap Scan on
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z d
> (cost=4.34..27.35 rows=7 width=4) (actual time=0.043..0.103 rows=7 loops=1)
> Recheck Cond: (6171 = id_euweek)
> Heap Blocks: exact=7
> -> Bitmap Index Scan on
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_a_id_euweek_idx
> (cost=0.00..4.33 rows=7 width=0) (actual time=0.036..0.036 rows=7 loops=1)
> Index Cond: (6171 = id_euweek)
> -> Bitmap Heap Scan on f_ticketupdate_aad5jtwal0ayaax f
> (cost=1633.90..214617.67 rows=87472 width=4) (actual time=0.003..0.003
> rows=0 loops=7)
> Recheck Cond: (dt_event_id = d.id)
> -> Bitmap Index Scan on
> f_ticketupdate_aad5jtwal0ayaax_dt_event_id_idx (cost=0.00..1612.03
> rows=87472 width=0) (actual time=0.003..0.003 rows=0 loops=7)
> Index Cond: (dt_event_id = d.id)
> Planning time: 0.496 ms
> Execution time: 0.227 ms
> (13 rows)
>
> db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# set
> max_parallel_workers_per_gather=2;
> SET
> db_sq3rjf5b7p309lq9wuqrh3qhk4gy9fbw=# explain analyze SELECT count(*)
> FROM f_ticketupdate_aad5jtwal0ayaax AS f
> INNER JOIN
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z AS d
> ON (f.dt_event_id = d.id)
> WHERE ( 6171 = d."id_euweek" );
>
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Finalize Aggregate (cost=1490623.06..1490623.07 rows=1 width=8) (actual
> time=9604.745..9604.745 rows=1 loops=1)
> -> Gather (cost=1490622.85..1490623.06 rows=2 width=8) (actual
> time=9604.707..9604.739 rows=3 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -> Partial Aggregate (cost=1489622.85..1489622.86 rows=1
> width=8) (actual time=9600.255..9600.255 rows=1 loops=3)
> -> Hash Join (cost=27.44..1489550.83 rows=28808 width=0)
> (actual time=9600.249..9600.249 rows=0 loops=3)
> Hash Cond: (f.dt_event_id = d.id)
> -> Parallel Seq Scan on
> f_ticketupdate_aad5jtwal0ayaax f (cost=0.00..1185867.47 rows=24054847
> width=4) (actual time=0.076..4955.525 rows=19243863 loops=3)
> -> Hash (cost=27.35..27.35 rows=7 width=4) (actual
> time=0.099..0.099 rows=7 loops=3)
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
> -> Bitmap Heap Scan on
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z d
> (cost=4.34..27.35 rows=7 width=4) (actual time=0.045..0.085 rows=7 loops=3)
> Recheck Cond: (6171 = id_euweek)
> Heap Blocks: exact=7
> -> Bitmap Index Scan on
> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_a_id_euweek_idx
> (cost=0.00..4.33 rows=7 width=0) (actual time=0.032..0.032 rows=7 loops=3)
> Index Cond: (6171 = id_euweek)
> Planning time: 0.616 ms
> Execution time: 9611.924 ms
> (17 rows)
>
> On Mon, Apr 16, 2018 at 4:53 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>> ---------- Forwarded message ---------
>> From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
>> Date: po 16. 4. 2018 16:14
>> Subject: Re: very slow queries when max_parallel_workers_per_gather is
>> higher than zero
>> To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
>>
>>
>> Apologies, the reduced query was missing a where condition on id_week:
>>
>> SELECT count(*)
>> FROM f_ticketupdate_aad5jtwal0ayaax AS f
>> INNER JOIN
>> dwh_dm_aabv5kk9rxac4lz_aaonw7nchsan2n1_aad8xhr0m_aaewg8j61iagl1z AS d
>> ON (f.dt_event_id = d.id)
>> WHERE ( 6171 = d."id_euweek" )
>>
>>
>> regards
>>
>>
>> --
>> Tomas Vondra http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>