explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M1cw : Optimization for: plan #dWCQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5,098.978 5,098.978 ↑ 5.0 205,446 1

CTE Scan on contas_total (cost=100,105.34..141,193.94 rows=1,027,215 width=573) (actual time=4,722.882..5,098.978 rows=205,446 loops=1)

2.          

CTE contas

3. 121.243 843.769 ↓ 1.0 205,446 1

Hash Left Join (cost=25,165.21..47,161.00 rows=205,443 width=94) (actual time=247.062..843.769 rows=205,446 loops=1)

  • Hash Cond: (cr.tipodocumento_codigo = t.codigo)
4. 33.211 722.511 ↓ 1.0 205,446 1

Hash Left Join (cost=25,163.72..43,307.45 rows=205,443 width=80) (actual time=247.037..722.511 rows=205,446 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
5. 54.696 689.295 ↓ 1.0 205,446 1

Hash Left Join (cost=25,162.63..42,535.94 rows=205,443 width=75) (actual time=247.028..689.295 rows=205,446 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6. 92.220 634.586 ↓ 1.0 205,446 1

Hash Left Join (cost=25,161.49..39,709.96 rows=205,443 width=68) (actual time=247.004..634.586 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7. 83.441 487.954 ↓ 1.0 205,446 1

Hash Left Join (cost=19,623.62..31,347.25 rows=205,443 width=64) (actual time=191.044..487.954 rows=205,446 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
8. 60.468 353.465 ↓ 1.0 205,446 1

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

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
9. 29.007 219.590 ↓ 1.0 205,446 1

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

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
10. 108.770 187.180 ↓ 1.0 205,446 1

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

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

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

12. 35.381 60.784 ↓ 1.0 205,446 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
13. 25.403 25.403 ↓ 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.006..25.403 rows=205,446 loops=1)

  • Filter: (contasareceber_codigo IS NOT NULL)
14. 0.042 3.403 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
15. 0.000 3.361 ↑ 1.0 176 1

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

16. 1.893 3.031 ↑ 1.0 176 1

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

  • Hash Cond: (snfpg.codigo = snfpg_cr.saidasnf_pagtos_codigo)
17. 1.090 1.090 ↑ 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.090 rows=22,122 loops=1)

18. 0.029 0.048 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
19. 0.019 0.019 ↑ 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.019 rows=176 loops=1)

20. 0.352 0.352 ↑ 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.002 rows=1 loops=176)

  • Index Cond: (snfpg.codsaidasnf = codigo)
21. 5.337 73.407 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
22. 20.877 68.070 ↑ 1.0 28,064 1

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

  • Hash Cond: (pv.codigo = pvpg.codprevenda)
23. 5.332 5.332 ↑ 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.332 rows=91,560 loops=1)

24. 4.606 41.861 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1353kB
25. 10.282 37.255 ↑ 1.0 28,064 1

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

  • Hash Cond: (pvpg_cr.prevendas_pagtos_codigo = pvpg.codigo)
26. 1.429 1.429 ↑ 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.003..1.429 rows=28,064 loops=1)

27. 15.987 25.544 ↑ 1.0 93,499 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4677kB
28. 9.557 9.557 ↑ 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.005..9.557 rows=93,499 loops=1)

29. 32.935 51.048 ↑ 1.0 177,399 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 8978kB
30. 18.113 18.113 ↑ 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..18.113 rows=177,399 loops=1)

31. 41.846 54.412 ↑ 1.0 205,639 1

Hash (cost=2,967.39..2,967.39 rows=205,639 width=8) (actual time=54.412..54.412 rows=205,639 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10081kB
32. 12.566 12.566 ↑ 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.008..12.566 rows=205,639 loops=1)

33. 0.009 0.013 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.004 0.004 ↑ 1.0 6 1

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

35. 0.003 0.005 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 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)

37. 0.012 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
38. 0.003 0.003 ↑ 1.0 22 1

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

39.          

CTE contas_saldo

40. 3,605.799 4,357.038 ↓ 205.4 205,446 1

Function Scan on contasareceber_saldo (cost=4,622.73..4,632.73 rows=1,000 width=76) (actual time=4,327.882..4,357.038 rows=205,446 loops=1)

41.          

Initplan (forFunction Scan)

42. 28.670 751.239 ↑ 1.0 1 1

Aggregate (cost=4,622.47..4,622.48 rows=1 width=4) (actual time=751.239..751.239 rows=1 loops=1)

43. 722.569 722.569 ↓ 1.0 205,446 1

CTE Scan on contas (cost=0.00..4,108.86 rows=205,443 width=4) (actual time=0.000..722.569 rows=205,446 loops=1)

44.          

CTE contas_total

45. 113.457 4,865.218 ↑ 5.0 205,446 1

Hash Left Join (cost=32.50..48,311.60 rows=1,027,215 width=573) (actual time=4,722.871..4,865.218 rows=205,446 loops=1)

  • Hash Cond: (vcr.codigo = cr_saldo.contasareceber_codigo)
46. 275.967 275.967 ↓ 1.0 205,446 1

CTE Scan on contas vcr (cost=0.00..4,108.86 rows=205,443 width=501) (actual time=247.064..275.967 rows=205,446 loops=1)

47. 43.009 4,475.794 ↓ 205.4 205,446 1

Hash (cost=20.00..20.00 rows=1,000 width=76) (actual time=4,475.794..4,475.794 rows=205,446 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 14082kB
48. 4,432.785 4,432.785 ↓ 205.4 205,446 1

CTE Scan on contas_saldo cr_saldo (cost=0.00..20.00 rows=1,000 width=76) (actual time=4,327.886..4,432.785 rows=205,446 loops=1)