pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Luís Roberto Weck <luisroberto@siscobra.com.br>
To: jeff.janes@gmail.com
Cc: pgsql-performance@lists.postgresql.org, Tomas Vondra <tomas.vondra@2ndquadrant.com>
Subject: Re: Parallel Query
Date: Thu, 14 Nov 2019 08:14:25 -0300
Message-ID: <9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br> (raw)
In-Reply-To: <CAMkU=1x6Zzpi4W2gjdf3uMyM3C2DH+7NizQp8LO6NWu_iY1eEQ@mail.gmail.com>
References: <732b7303-8b60-2f33-7f00-3b0cd70a7c5b@siscobra.com.br>
	<20191113204737.l6uny3qdwl6a4u7m@development>
	<81c26220-3897-2d87-fb40-35050698f0a1@siscobra.com.br>
	<CAMkU=1x6Zzpi4W2gjdf3uMyM3C2DH+7NizQp8LO6NWu_iY1eEQ@mail.gmail.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

view thread (9+ messages)

Message-ID: <9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br>
Permalink:  ../9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br/
Also on:    postgresql.org/message-id/9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: luisroberto@siscobra.com.br, jeff.janes@gmail.com, tomas.vondra@2ndquadrant.com
  Subject: Re: Parallel Query
  In-Reply-To: <9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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