Em 13/11/2019 19:08, Jeff Janes escreveu:
On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck
<luisrobe...@siscobra.com.br <mailto:luisrobe...@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