Re: Parallel Query

Em 13/11/2019 19:08, Jeff Janes escreveu:
> On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck 
> <[email protected] <mailto:[email protected]>> 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