Re: very slow queries when max_parallel_workers_per_gather is higherthan 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.stehule@gmail.com> wrote:

---------- Forwarded message ---------
From: Tomas Vondra <tomas.vondra@2ndquadrant.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.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.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