Postgres Pro
Downloads
Home   >   mailing lists

Re: very slow queries when max_parallel_workers_per_gather is higherthan zero - Mailing list pgsql-hackers

From Guilherme Pereira
Subject Re: very slow queries when max_parallel_workers_per_gather is higherthan zero
Date
Msg-id CAC4tJuiac3fczf6P+AfDCNBF9qynCw9d7q_VHdbWmPd2xkX7rw@mail.gmail.com
Whole thread Raw
In response to Re: very slow queries when max_parallel_workers_per_gather is higherthan zero  (Guilherme Pereira <guilherme.pereira@gooddata.com>)
List pgsql-hackers
Tree view
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.pereira@gooddata.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.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

pgsql-hackers by date:

Previous
From: "Daniel Verite"
Date:
Subject: Re: Proposal: Adding json logging
Next
From: David Arnold
Date:
Subject: Re: Proposal: Adding json logging