Re: very slow queries when max_parallel_workers_per_gather is higher than zero

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
>