Re: Parallel Query

Luís Roberto Weck <luisroberto@xxxxxxxxxxxxxxx> · Thu, 14 Nov 2019 08:14:25 -0300



    Em 13/11/2019 19:08, Jeff Janes escreveu:

    
        On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck
          <luisroberto@xxxxxxxxxxxxxxx>
          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