explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b2SW : Optimization for: plan #dWCQ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,638.159 5,638.159 ↓ 1.0 205,446 1

CTE Scan on contas_total (cost=55,401.00..63,618.72 rows=205,443 width=577) (actual time=4,716.617..5,638.159 rows=205,446 loops=1)

2.          

CTE contas

3. 133.954 5,138.466 ↓ 1.0 205,446 1

Hash Left Join (cost=33,968.32..49,751.32 rows=205,443 width=166) (actual time=4,716.604..5,138.466 rows=205,446 loops=1)

  • Hash Cond: (cr.tipodocumento_codigo = t.codigo)
4. 31.594 5,004.497 ↓ 1.0 205,446 1

Hash Left Join (cost=33,966.83..45,897.77 rows=205,443 width=152) (actual time=4,716.579..5,004.497 rows=205,446 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
5. 49.149 4,972.892 ↓ 1.0 205,446 1

Hash Left Join (cost=33,965.74..45,126.26 rows=205,443 width=147) (actual time=4,716.564..4,972.892 rows=205,446 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6. 77.965 4,923.729 ↓ 1.0 205,446 1

Hash Left Join (cost=33,964.60..42,300.28 rows=205,443 width=140) (actual time=4,716.537..4,923.729 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
7. 114.207 4,797.292 ↓ 1.0 205,446 1

Hash Right Join (cost=29,013.13..34,806.09 rows=205,443 width=136) (actual time=4,666.981..4,797.292 rows=205,446 loops=1)

  • Hash Cond: (cr_sit.contasareceber_codigo = cr.codigo)
8. 17.393 17.393 ↑ 1.0 205,639 1

Seq Scan on contasareceber_situacao cr_sit (cost=0.00..2,967.39 rows=205,639 width=8) (actual time=0.009..17.393 rows=205,639 loops=1)

9. 83.934 4,665.692 ↓ 1.0 205,446 1

Hash (cost=26,445.09..26,445.09 rows=205,443 width=132) (actual time=4,665.692..4,665.692 rows=205,446 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 24234kB
10. 110.829 4,581.758 ↓ 1.0 205,446 1

Hash Right Join (cost=26,421.35..26,445.09 rows=205,443 width=132) (actual time=4,461.918..4,581.758 rows=205,446 loops=1)

  • Hash Cond: (cr_saldo.contasareceber_codigo = cr.codigo)
11. 4,071.818 4,071.818 ↓ 205.4 205,446 1

Function Scan on contasareceber_saldo cr_saldo (cost=0.26..10.26 rows=1,000 width=76) (actual time=4,061.694..4,071.818 rows=205,446 loops=1)

12. 73.308 399.111 ↓ 1.0 205,446 1

Hash (cost=23,853.06..23,853.06 rows=205,443 width=60) (actual time=399.111..399.111 rows=205,446 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 19423kB
13. 54.666 325.803 ↓ 1.0 205,446 1

Hash Left Join (cost=14,672.14..23,853.06 rows=205,443 width=60) (actual time=136.996..325.803 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
14. 27.328 202.108 ↓ 1.0 205,446 1

Hash Left Join (cost=6,428.25..14,558.39 rows=205,443 width=52) (actual time=67.780..202.108 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
15. 95.947 171.455 ↓ 1.0 205,446 1

Hash Join (cost=5,733.47..13,091.43 rows=205,443 width=44) (actual time=64.446..171.455 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
16. 12.171 12.171 ↑ 1.0 205,639 1

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=44) (actual time=0.007..12.171 rows=205,639 loops=1)

17. 37.633 63.337 ↓ 1.0 205,446 1

Hash (cost=3,165.43..3,165.43 rows=205,443 width=4) (actual time=63.337..63.337 rows=205,446 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
18. 25.704 25.704 ↓ 1.0 205,446 1

Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..3,165.43 rows=205,443 width=4) (actual time=0.004..25.704 rows=205,446 loops=1)

  • Filter: (contasareceber_codigo IS NOT NULL)
19. 0.035 3.325 ↑ 1.0 176 1

Hash (cost=692.58..692.58 rows=176 width=12) (actual time=3.325..3.325 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
20. 0.105 3.290 ↑ 1.0 176 1

Nested Loop Left Join (cost=5.25..692.58 rows=176 width=12) (actual time=2.213..3.290 rows=176 loops=1)

21. 1.883 3.009 ↑ 1.0 176 1

Hash Right Join (cost=4.96..605.16 rows=176 width=8) (actual time=2.202..3.009 rows=176 loops=1)

  • Hash Cond: (snfpg.codigo = snfpg_cr.saidasnf_pagtos_codigo)
22. 1.085 1.085 ↑ 1.0 22,122 1

Seq Scan on saidasnf_pagto snfpg (cost=0.00..377.22 rows=22,122 width=8) (actual time=0.002..1.085 rows=22,122 loops=1)

23. 0.023 0.041 ↑ 1.0 176 1

Hash (cost=2.76..2.76 rows=176 width=8) (actual time=0.041..0.041 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.018 0.018 ↑ 1.0 176 1

Seq Scan on saidasnf_pagtos_contasareceber snfpg_cr (cost=0.00..2.76 rows=176 width=8) (actual time=0.003..0.018 rows=176 loops=1)

25. 0.176 0.176 ↑ 1.0 1 176

Index Scan using saidasnf_codigo_index on saidasnf snf (cost=0.29..0.49 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=176)

  • Index Cond: (snfpg.codsaidasnf = codigo)
26. 5.273 69.029 ↑ 1.0 28,064 1

Hash (cost=7,893.09..7,893.09 rows=28,064 width=12) (actual time=69.029..69.029 rows=28,064 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
27. 19.992 63.756 ↑ 1.0 28,064 1

Hash Right Join (cost=3,859.05..7,893.09 rows=28,064 width=12) (actual time=38.500..63.756 rows=28,064 loops=1)

  • Hash Cond: (pv.codigo = pvpg.codprevenda)
28. 5.406 5.406 ↑ 1.0 91,560 1

Seq Scan on prevendas pv (cost=0.00..3,295.60 rows=91,560 width=8) (actual time=0.001..5.406 rows=91,560 loops=1)

29. 4.392 38.358 ↑ 1.0 28,064 1

Hash (cost=3,508.25..3,508.25 rows=28,064 width=8) (actual time=38.358..38.358 rows=28,064 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1353kB
30. 9.729 33.966 ↑ 1.0 28,064 1

Hash Left Join (cost=2,716.73..3,508.25 rows=28,064 width=8) (actual time=23.347..33.966 rows=28,064 loops=1)

  • Hash Cond: (pvpg_cr.prevendas_pagtos_codigo = pvpg.codigo)
31. 1.492 1.492 ↑ 1.0 28,064 1

Seq Scan on prevendas_pagtos_contasareceber pvpg_cr (cost=0.00..405.64 rows=28,064 width=8) (actual time=0.002..1.492 rows=28,064 loops=1)

32. 13.824 22.745 ↑ 1.0 93,499 1

Hash (cost=1,547.99..1,547.99 rows=93,499 width=8) (actual time=22.745..22.745 rows=93,499 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4677kB
33. 8.921 8.921 ↑ 1.0 93,499 1

Seq Scan on prevenda_pagto pvpg (cost=0.00..1,547.99 rows=93,499 width=8) (actual time=0.002..8.921 rows=93,499 loops=1)

34. 31.452 48.472 ↑ 1.0 177,399 1

Hash (cost=2,733.99..2,733.99 rows=177,399 width=8) (actual time=48.472..48.472 rows=177,399 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8978kB
35. 17.020 17.020 ↑ 1.0 177,399 1

Seq Scan on contasareceber_avulsas crav (cost=0.00..2,733.99 rows=177,399 width=8) (actual time=0.004..17.020 rows=177,399 loops=1)

36. 0.011 0.014 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=15) (actual time=0.014..0.014 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on situacoescontas sc (cost=0.00..1.06 rows=6 width=15) (actual time=0.002..0.003 rows=6 loops=1)

38. 0.009 0.011 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=13) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on gruposcontas grct (cost=0.00..1.04 rows=4 width=13) (actual time=0.002..0.002 rows=4 loops=1)

40. 0.011 0.015 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=18) (actual time=0.015..0.015 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.004 0.004 ↑ 1.0 22 1

Seq Scan on tipodocumento t (cost=0.00..1.22 rows=22 width=18) (actual time=0.002..0.004 rows=22 loops=1)

42.          

CTE contas_total

43. 5,370.907 5,370.907 ↓ 1.0 205,446 1

CTE Scan on contas vcr (cost=0.00..5,649.68 rows=205,443 width=573) (actual time=4,716.609..5,370.907 rows=205,446 loops=1)