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
> <luisroberto(at)siscobra(dot)com(dot)br <mailto:luisroberto(at)siscobra(dot)com(dot)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