Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iVD1H-0005rt-0L for pgsql-performance@arkaria.postgresql.org; Thu, 14 Nov 2019 11:10:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iVD0H-0004Lj-GQ for pgsql-performance@arkaria.postgresql.org; Thu, 14 Nov 2019 11:09:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iVD0H-0004La-6z for pgsql-performance@lists.postgresql.org; Thu, 14 Nov 2019 11:09:33 +0000 Received: from mail49186.hm1315.locaweb.com.br ([201.76.49.186]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iVD02-0001V4-Oq for pgsql-performance@lists.postgresql.org; Thu, 14 Nov 2019 11:09:30 +0000 Received: from mcbain0008.email.locaweb.com.br (189.126.112.84) by mail4950.hm1315.locaweb.com.br id hpkqui16a0ov for ; Thu, 14 Nov 2019 08:09:22 -0300 (envelope-from ) Received: from proxy.email-ssl.com.br (bartf0026.email.locaweb.com.br [10.31.120.58]) by mcbain0008.email.locaweb.com.br (Postfix) with ESMTP id 85E4619FB42; Thu, 14 Nov 2019 08:08:20 -0300 (-03) x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9IgCFmgQGgrxCDEo15F9LB44HxULpKL96koO7pjBJFKBGZincnXNnevfTOHd8ZS-TT1W3Ut_AM6n2tA-l9dP52n0ZV7w9aE3mXwQoPyYaTAEdErbyXTLRJ3KBYWjlEvQTcM= NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9IgCFmgQGgrxCDEo15F9LB44HxULpKL96koO7pjBJFKBGZincnXNnevfTOHd8ZS-TT1W3Ut_AM6n2tA-l9dP52n0ZV7w9aE3mXwQoPyYaTAEdErbyXTLRJ3KBYWjlEvQTcM= NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy x-locaweb-id: qyHzKK_R8aWhjwoIerpCUhsi5PfCUymhK53qXIAegdcRogeYX3-XTadcSpgGGxitWQf3rKGbFuqvLmn5XdJ_XeDnSC7FYZn7HbLXpGF-9IgCFmgQGgrxCDEo15F9LB44HxULpKL96koO7pjBJFKBGZincnXNnevfTOHd8ZS-TT1W3Ut_AM6n2tA-l9dP52n0ZV7w9aE3mXwQoPyYaTAEdErbyXTLRJ3KBYWjlEvQTcM= NmM3NTY5NzM3MjZmNjI2NTcyNzQ2ZjQwNzM2OTczNjM2ZjYyNzI2MTJlNjM2ZjZkMmU2Mjcy X-LocaWeb-COR: locaweb_2009_x-mail X-AuthUser: luisroberto@siscobra.com.br Received: from [192.168.0.42] (unknown [189.45.198.123]) (Authenticated sender: luisroberto@siscobra.com.br) by proxy.email-ssl.com.br (Postfix) with ESMTPSA id 88462E002E2; Thu, 14 Nov 2019 09:09:08 -0200 (-02) Cc: pgsql-performance@lists.postgresql.org, Tomas Vondra Subject: Re: Parallel Query To: jeff.janes@gmail.com References: <732b7303-8b60-2f33-7f00-3b0cd70a7c5b@siscobra.com.br> <20191113204737.l6uny3qdwl6a4u7m@development> <81c26220-3897-2d87-fb40-35050698f0a1@siscobra.com.br> From: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= Message-ID: <9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br> Date: Thu, 14 Nov 2019 08:14:25 -0300 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A0AB6A3AF3A4C8B628DB4AB6" Content-Language: pt-BR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A0AB6A3AF3A4C8B628DB4AB6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Em 13/11/2019 19:08, Jeff Janes escreveu: > On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck > > 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 --------------A0AB6A3AF3A4C8B628DB4AB6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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> 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
--------------A0AB6A3AF3A4C8B628DB4AB6--