explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QNVb : Optimization for: plan #1ICS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 155.194 4,355.694 ↑ 2.5 205,443 1

Sort (cost=233,531.08..234,816.33 rows=514,100 width=377) (actual time=4,338.240..4,355.694 rows=205,443 loops=1)

  • Sort Key: contas_total.datavencimento DESC, contas_total.situacao
  • Sort Method: quicksort Memory: 35035kB
2.          

CTE contas

3. 105.642 1,094.652 ↓ 2.0 205,443 1

Subquery Scan on vcontasareceber (cost=43,317.74..50,001.01 rows=102,820 width=318) (actual time=250.223..1,094.652 rows=205,443 loops=1)

  • Filter: vcontasareceber.faturada
  • Rows Removed by Filter: 196
4. 989.010 989.010 ↑ 1.0 205,639 1

CTE Scan on contas (cost=43,317.74..47,430.52 rows=205,639 width=351) (actual time=250.216..989.010 rows=205,639 loops=1)

5.          

CTE contas

6. 78.843 785.019 ↑ 1.0 205,639 1

Hash Left Join (cost=25,163.72..43,317.74 rows=205,639 width=111) (actual time=250.212..785.019 rows=205,639 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
7. 62.240 706.165 ↑ 1.0 205,639 1

Hash Left Join (cost=25,162.63..42,545.50 rows=205,639 width=102) (actual time=250.194..706.165 rows=205,639 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
8. 93.274 643.910 ↑ 1.0 205,639 1

Hash Left Join (cost=25,161.49..39,716.82 rows=205,639 width=89) (actual time=250.169..643.910 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
9. 76.833 483.027 ↑ 1.0 205,639 1

Hash Left Join (cost=19,623.62..31,351.41 rows=205,639 width=85) (actual time=181.474..483.027 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
10. 85.516 352.778 ↑ 1.0 205,639 1

Hash Left Join (cost=14,672.14..23,854.80 rows=205,639 width=77) (actual time=126.946..352.778 rows=205,639 loops=1)

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

Hash Left Join (cost=8,938.67..15,295.75 rows=205,639 width=73) (actual time=73.594..215.096 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
12. 59.647 81.445 ↑ 1.0 205,639 1

Hash Left Join (cost=694.78..6,000.08 rows=205,639 width=61) (actual time=3.476..81.445 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
13. 18.339 18.339 ↑ 1.0 205,639 1

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=53) (actual time=0.012..18.339 rows=205,639 loops=1)

14. 0.046 3.459 ↑ 1.0 176 1

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

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

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

16. 1.940 3.076 ↑ 1.0 176 1

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

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

18. 0.029 0.044 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
19. 0.015 0.015 ↑ 1.0 176 1

Seq Scan on saidasnf_pagtos_contasareceber snfpg_cr (cost=0.00..2.76 rows=176 width=8) (actual time=0.002..0.015 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.328 69.966 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1572kB
22. 19.909 64.638 ↑ 1.0 28,064 1

Hash Right Join (cost=3,859.05..7,893.09 rows=28,064 width=16) (actual time=39.477..64.638 rows=28,064 loops=1)

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

24. 4.595 39.306 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
25. 9.445 34.711 ↑ 1.0 28,064 1

Hash Left Join (cost=2,716.73..3,508.25 rows=28,064 width=12) (actual time=24.485..34.711 rows=28,064 loops=1)

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

27. 15.093 23.882 ↑ 1.0 93,499 1

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

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

29. 34.026 52.166 ↑ 1.0 205,443 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
30. 18.140 18.140 ↑ 1.0 205,443 1

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

31. 35.569 53.416 ↑ 1.0 177,399 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9671kB
32. 17.847 17.847 ↑ 1.0 177,399 1

Seq Scan on contasareceber_avulsas crav (cost=0.00..2,733.99 rows=177,399 width=12) (actual time=0.003..17.847 rows=177,399 loops=1)

33. 53.735 67.609 ↑ 1.0 205,639 1

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

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

35. 0.011 0.015 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=17) (actual time=0.015..0.015 rows=6 loops=1)

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

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

37. 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
38. 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)

39.          

CTE contas_saldo

40. 2,463.661 3,705.091 ↓ 205.4 205,443 1

Function Scan on contasareceber_saldo (cost=2,313.72..2,323.72 rows=1,000 width=164) (actual time=3,696.476..3,705.091 rows=205,443 loops=1)

41.          

Initplan (forFunction Scan)

42. 30.345 1,241.430 ↑ 1.0 1 1

Aggregate (cost=2,313.45..2,313.46 rows=1 width=4) (actual time=1,241.430..1,241.430 rows=1 loops=1)

43. 1,211.085 1,211.085 ↓ 2.0 205,443 1

CTE Scan on contas contas_1 (cost=0.00..2,056.40 rows=102,820 width=4) (actual time=250.224..1,211.085 rows=205,443 loops=1)

44.          

CTE contas_total

45. 90.819 4,004.445 ↑ 2.5 205,443 1

Merge Right Join (cost=10,685.87..22,258.12 rows=514,100 width=381) (actual time=3,896.221..4,004.445 rows=205,443 loops=1)

  • Merge Cond: (cr_saldo.contasareceber_codigo = vcr.codigo)
46. 61.147 3,828.939 ↓ 205.4 205,443 1

Sort (cost=69.83..72.33 rows=1,000 width=76) (actual time=3,820.501..3,828.939 rows=205,443 loops=1)

  • Sort Key: cr_saldo.contasareceber_codigo
  • Sort Method: quicksort Memory: 22195kB
47. 3,767.792 3,767.792 ↓ 205.4 205,443 1

CTE Scan on contas_saldo cr_saldo (cost=0.00..20.00 rows=1,000 width=76) (actual time=3,696.479..3,767.792 rows=205,443 loops=1)

48. 62.698 84.687 ↓ 2.0 205,443 1

Sort (cost=10,616.04..10,873.09 rows=102,820 width=309) (actual time=75.707..84.687 rows=205,443 loops=1)

  • Sort Key: vcr.codigo
  • Sort Method: quicksort Memory: 35035kB
49. 21.989 21.989 ↓ 2.0 205,443 1

CTE Scan on contas vcr (cost=0.00..2,056.40 rows=102,820 width=309) (actual time=0.001..21.989 rows=205,443 loops=1)

50. 4,200.500 4,200.500 ↑ 2.5 205,443 1

CTE Scan on contas_total (cost=0.00..20,564.00 rows=514,100 width=377) (actual time=3,896.231..4,200.500 rows=205,443 loops=1)