pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: parallel query
9+ messages / 4 participants
[nested] [flat]
* Re: parallel query
@ 2019-04-02 11:28 Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2019-04-02 11:28 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-performance@lists.postgresql.org
Mariel Cherkassky wrote:
> I wanted to a few questions regarding the parallel parameters : max_worker_processes and max_parallel_workers_per_gather.
>
> 1)Basically, max_worker_processes should be set to the number of cpus I have in the machine ?
Setting it higher would not be smart.
Setting it lower can also be a good idea; it depends
on your workload.
> 2)If I set max_worker_processes to X and max_parallel_workers_per_gather to Y (X>Y)
> it means that I will have at max (X/2) queries that can run in parallel. Am I right ?
> For example, max_worker_processes =8,max_parallel_workers_per_gather =4, it means
> that at max I can have 4 queries that are running in parallel ? and at min 2 queries
> (or none) can run in parallel ?
That is correct, but unless you set "max_parallel_workers_per_gather" to 1, one
query can use more than one parallel worker, and then you can have fewer
concurrent queries.
It also depends on the size of the table or index how many workers PostgreSQL will use.
> 3)So If I calculate my work_mem based on the number of sessions I have : (TOTAL_MEM/2/NUM_OF_CONNECTIONS)
> I should add 8 to the NUM_OF_CONNECTIONS to have a new value for the work_mem in order to consider queries that run in parallel..
Yes, but don't forget that one query can use "work_mem" several times if the
execution plan has several memory intensive nodes.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Parallel Query
@ 2019-11-13 20:16 Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:40 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
2019-11-13 20:47 ` Re: Parallel Query Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 9+ messages in thread
From: Luís Roberto Weck @ 2019-11-13 20:16 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi!
Is there a reason query 3 can't use parallel workers? Using q1 and q2
they seem very similar but can use up to 4 workers to run faster:
q1: https://pastebin.com/ufkbSmfB
q2: https://pastebin.com/Yt32zRNX
q3: https://pastebin.com/dqh7yKPb
The sort node on q3 takes almost 12 seconds, making the query run on 68
if I had set enough work_mem to make it all in memory.
Running version 10.10.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
@ 2019-11-13 20:40 ` Jeff Janes <jeff.janes@gmail.com>
2019-11-13 21:07 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2019-11-13 20:40 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Nov 13, 2019 at 3:11 PM Luís Roberto Weck <
luisroberto@siscobra.com.br> wrote:
> Hi!
>
> Is there a reason query 3 can't use parallel workers? Using q1 and q2
> they seem very similar but can use up to 4 workers to run faster:
>
> q1: https://pastebin.com/ufkbSmfB
> q2: https://pastebin.com/Yt32zRNX
> q3: https://pastebin.com/dqh7yKPb
>
> The sort node on q3 takes almost 12 seconds, making the query run on 68
> if I had set enough work_mem to make it all in memory.
>
The third one thinks it is going find 3454539 output rows. If it run in
parallel, it thinks it will be passing lots of rows up from the parallel
workers, and charges a high price (parallel_tuple_cost = 0.1) for doing
so. So you can try lowering parallel_tuple_cost, or figuring out why the
estimate is so bad.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:40 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
@ 2019-11-13 21:07 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 22:21 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Luís Roberto Weck @ 2019-11-13 21:07 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
****
Em 13/11/2019 17:40, Jeff Janes escreveu:
> On Wed, Nov 13, 2019 at 3:11 PM Luís Roberto Weck
> <luisroberto@siscobra.com.br <mailto:luisroberto@siscobra.com.br>> wrote:
>
> Hi!
>
> Is there a reason query 3 can't use parallel workers? Using q1 and q2
> they seem very similar but can use up to 4 workers to run faster:
>
> q1: https://pastebin.com/ufkbSmfB
> q2: https://pastebin.com/Yt32zRNX
> q3: https://pastebin.com/dqh7yKPb
>
> The sort node on q3 takes almost 12 seconds, making the query run
> on 68
> if I had set enough work_mem to make it all in memory.
>
>
> The third one thinks it is going find 3454539 output rows. If it run
> in parallel, it thinks it will be passing lots of rows up from the
> parallel workers, and charges a high price (parallel_tuple_cost = 0.1)
> for doing so. So you can try lowering parallel_tuple_cost, or
> figuring out why the estimate is so bad.
>
> Cheers,
>
> Jeff
Hi Jeff,
I don't think the "HAVING" clause is havin any effect on the estimates:
WITHOUT "HAVING":
Group (cost=1245134.08..1279680.28 rows=3454620 width=14)
Group Key: c.concod, cp.conparnum, cp.conpardatven
-> Sort (cost=1245134.08..1253770.63 rows=3454620 width=14)
Sort Key: c.concod, cp.conparnum, cp.conpardatven
-> Hash Join (cost=34366.64..869960.70 rows=3454620 width=14)
Hash Cond: (cp.concod = c.concod)
-> Seq Scan on contrato_parcela cp (cost=0.00..804248.08
rows=11941308 width=14)
-> Hash (cost=23436.55..23436.55 rows=874407 width=8)
-> Index Only Scan using contrato_iu0004 on
contrato c (cost=0.43..23436.55 rows=874407 width=8)
Index Cond: (carcod = 100)
WITH "HAVING":
GroupAggregate (cost=1245144.88..1322874.51 rows=3454650 width=14)
Group Key: c.concod, cp.conparnum, cp.conpardatven
Filter: (count(*) > 1)
-> Sort (cost=1245144.88..1253781.51 rows=3454650 width=14)
Sort Key: c.concod, cp.conparnum, cp.conpardatven
-> Hash Join (cost=34366.64..869968.02 rows=3454650 width=14)
Hash Cond: (cp.concod = c.concod)
-> Seq Scan on contrato_parcela cp (cost=0.00..804255.13
rows=11941413 width=14)
-> Hash (cost=23436.55..23436.55 rows=874407 width=8)
-> Index Only Scan using contrato_iu0004 on
contrato c (cost=0.43..23436.55 rows=874407 width=8)
Index Cond: (carcod = 100)
Maybe PostgreSQL can't find a way to calculate having estimates?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:40 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
2019-11-13 21:07 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
@ 2019-11-13 22:21 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Janes @ 2019-11-13 22:21 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Nov 13, 2019 at 4:01 PM Luís Roberto Weck <
luisroberto@siscobra.com.br> wrote:
>
> Maybe PostgreSQL can't find a way to calculate having estimates?
>
I wasn't even thinking of the HAVING estimates I was thinking of just the
raw aggregates. It can't implement the HAVING until has the raw aggregate
in hand. But, what is the actual row count without the HAVING? Well, I
notice now this line:
Rows Removed by Filter: 6787359
So the row count of rows=86 is mostly due to the HAVING, not due to the raw
aggregation, a point I overlooked initially. So the planner is not
mistaken in thinking that a huge number of rows need to be passed up--it is
correct in thinking that.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
@ 2019-11-13 20:47 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-13 21:04 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2019-11-13 20:47 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Nov 13, 2019 at 05:16:44PM -0300, Luís Roberto Weck wrote:
>Hi!
>
>Is there a reason query 3 can't use parallel workers? Using q1 and q2
>they seem very similar but can use up to 4 workers to run faster:
>
>q1: https://pastebin.com/ufkbSmfB
>q2: https://pastebin.com/Yt32zRNX
>q3: https://pastebin.com/dqh7yKPb
>
>The sort node on q3 takes almost 12 seconds, making the query run on
>68 if I had set enough work_mem to make it all in memory.
>
Most likely because it'd be actually slower. The trouble is the
aggregation does not actually reduce the cardinality, or at least the
planner does not expect that - the Sort and GroupAggregate are expected
to produce 3454539 rows. The last step of the aggregation has to receive
and merge data from all workers, which is not exactly free, and if there
is no reduction of cardinality it's likely cheaper to just do everything
in a single process serially.
How does the explain analyze output look like without the HAVING clause?
Try setting parallel_setup_cost and parallel_tuple_cost to 0. That might
trigger parallel query.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:47 ` Re: Parallel Query Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-11-13 21:04 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 22:08 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Luís Roberto Weck @ 2019-11-13 21:04 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
****
Em 13/11/2019 17:47, Tomas Vondra escreveu:
> On Wed, Nov 13, 2019 at 05:16:44PM -0300, Luís Roberto Weck wrote:
>> Hi!
>>
>> Is there a reason query 3 can't use parallel workers? Using q1 and q2
>> they seem very similar but can use up to 4 workers to run faster:
>>
>> q1: https://pastebin.com/ufkbSmfB
>> q2: https://pastebin.com/Yt32zRNX
>> q3: https://pastebin.com/dqh7yKPb
>>
>> The sort node on q3 takes almost 12 seconds, making the query run on
>> 68 if I had set enough work_mem to make it all in memory.
>>
>
> Most likely because it'd be actually slower. The trouble is the
> aggregation does not actually reduce the cardinality, or at least the
> planner does not expect that - the Sort and GroupAggregate are expected
> to produce 3454539 rows. The last step of the aggregation has to receive
> and merge data from all workers, which is not exactly free, and if there
> is no reduction of cardinality it's likely cheaper to just do everything
> in a single process serially.
>
> How does the explain analyze output look like without the HAVING clause?
>
> Try setting parallel_setup_cost and parallel_tuple_cost to 0. That might
> trigger parallel query.
>
> regards
>
Tomas,
EXPLAIN:
Group (cost=1245130.37..1279676.46 rows=3454609 width=14)
Group Key: c.concod, cp.conparnum, cp.conpardatven
-> Sort (cost=1245130.37..1253766.89 rows=3454609 width=14)
Sort Key: c.concod, cp.conparnum, cp.conpardatven
-> Hash Join (cost=34366.64..869958.26 rows=3454609 width=14)
Hash Cond: (cp.concod = c.concod)
-> Seq Scan on contrato_parcela cp (cost=0.00..804245.73
rows=11941273 width=14)
-> Hash (cost=23436.55..23436.55 rows=874407 width=8)
-> Index Only Scan using contrato_iu0004 on
contrato c (cost=0.43..23436.55 rows=874407 width=8)
Index Cond: (carcod = 100)
EXPLAIN ANALYZE:
Group (cost=1245132.29..1279678.44 rows=3454615 width=14) (actual
time=61860.985..64852.579 rows=6787445 loops=1)
Group Key: c.concod, cp.conparnum, cp.conpardatven
-> Sort (cost=1245132.29..1253768.83 rows=3454615 width=14) (actual
time=61860.980..63128.557 rows=6787531 loops=1)
Sort Key: c.concod, cp.conparnum, cp.conpardatven
Sort Method: external merge Disk: 172688kB
-> Hash Join (cost=34366.64..869959.48 rows=3454615 width=14)
(actual time=876.428..52675.140 rows=6787531 loops=1)
Hash Cond: (cp.concod = c.concod)
-> Seq Scan on contrato_parcela cp (cost=0.00..804246.91
rows=11941291 width=14) (actual time=0.010..44860.242 rows=11962505 loops=1)
-> Hash (cost=23436.55..23436.55 rows=874407 width=8)
(actual time=874.791..874.791 rows=879841 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 42561kB
-> Index Only Scan using contrato_iu0004 on
contrato c (cost=0.43..23436.55 rows=874407 width=8) (actual
time=0.036..535.897 rows=879841 loops=1)
Index Cond: (carcod = 100)
Heap Fetches: 144438
Planning time: 1.252 ms
Execution time: 65214.007 ms
Indeed, reducing the costs made the query run in parallel, but the
improvement in speed was not worth the cost (CPU).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:47 ` Re: Parallel Query Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-13 21:04 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
@ 2019-11-13 22:08 ` Jeff Janes <jeff.janes@gmail.com>
2019-11-14 11:14 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2019-11-13 22:08 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck <
luisroberto@siscobra.com.br> wrote:
>
>
> Indeed, reducing the costs made the query run in parallel, but the
> improvement in speed was not worth the cost (CPU).
>
Could you show the plan for that?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Parallel Query
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:47 ` Re: Parallel Query Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-13 21:04 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 22:08 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
@ 2019-11-14 11:14 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 0 replies; 9+ messages in thread
From: Luís Roberto Weck @ 2019-11-14 11:14 UTC (permalink / raw)
To: jeff.janes@gmail.com; +Cc: pgsql-performance@lists.postgresql.org, Tomas Vondra <tomas.vondra@2ndquadrant.com>
Em 13/11/2019 19:08, Jeff Janes escreveu:
> On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck
> <luisroberto@siscobra.com.br <mailto:luisroberto@siscobra.com.br>> wrote:
>
>
>
> Indeed, reducing the costs made the query run in parallel, but the
> improvement in speed was not worth the cost (CPU).
>
>
> Could you show the plan for that?
Sure:
Finalize GroupAggregate (cost=842675.56..1017018.29 rows=3470567
width=14) (actual time=61419.510..65635.188 rows=86 loops=1)
Group Key: c.concod, cp.conparnum, cp.conpardatven
Filter: (count(*) > 1)
Rows Removed by Filter: 6787359
-> Gather Merge (cost=842675.56..947606.94 rows=3470568 width=22)
(actual time=51620.609..60648.085 rows=6787506 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=842575.50..862097.45
rows=867642 width=22) (actual time=51585.526..53477.065 rows=1357501
loops=5)
Group Key: c.concod, cp.conparnum, cp.conpardatven
-> Sort (cost=842575.50..844744.61 rows=867642
width=14) (actual time=51585.514..51951.984 rows=1357506 loops=5)
Sort Key: c.concod, cp.conparnum, cp.conpardatven
Sort Method: quicksort Memory: 112999kB
-> Hash Join (cost=34390.13..756996.76 rows=867642
width=14) (actual time=1087.591..49744.673 rows=1357506 loops=5)
Hash Cond: (cp.concod = c.concod)
-> Parallel Seq Scan on contrato_parcela cp
(cost=0.00..714762.89 rows=2988089 width=14) (actual
time=0.077..46674.986 rows=2392501 loops=5)
-> Hash (cost=23462.75..23462.75 rows=874190
width=8) (actual time=1080.189..1080.189 rows=879841 loops=5)
Buckets: 1048576 Batches: 1 Memory
Usage: 42561kB
-> Index Only Scan using
contrato_iu0004 on contrato c (cost=0.43..23462.75 rows=874190 width=8)
(actual time=0.141..663.108 rows=879841 loops=5)
Index Cond: (carcod = 100)
Heap Fetches: 35197
Planning time: 1.045 ms
Execution time: 65734.134 ms
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-11-14 11:14 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-02 11:28 Re: parallel query Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-13 20:16 Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 20:40 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
2019-11-13 21:07 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 22:21 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
2019-11-13 20:47 ` Re: Parallel Query Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-13 21:04 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-11-13 22:08 ` Re: Parallel Query Jeff Janes <jeff.janes@gmail.com>
2019-11-14 11:14 ` Re: Parallel Query Luís Roberto Weck <luisroberto@siscobra.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox