explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1ICS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 159.783 4,412.610 ↑ 2.5 205,443 1

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

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

CTE contas

3. 105.530 1,126.190 ↓ 2.0 205,443 1

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

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

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

5.          

CTE contas

6. 79.714 815.637 ↑ 1.0 205,639 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

14. 0.038 3.417 ↑ 1.0 176 1

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

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

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

16. 1.899 3.038 ↑ 1.0 176 1

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

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

18. 0.028 0.045 ↑ 1.0 176 1

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

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

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

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

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

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

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

24. 4.845 42.464 ↑ 1.0 28,064 1

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

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

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

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

27. 16.637 25.939 ↑ 1.0 93,499 1

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

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

29. 38.807 85.401 ↑ 1.0 205,443 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9271kB
30. 46.594 46.594 ↑ 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.004..46.594 rows=205,443 loops=1)

31. 37.367 56.817 ↑ 1.0 177,399 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9671kB
32. 19.450 19.450 ↑ 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.004..19.450 rows=177,399 loops=1)

33. 41.064 52.465 ↑ 1.0 205,639 1

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

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

35. 0.003 0.007 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=17) (actual time=0.007..0.007 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.002..0.004 rows=6 loops=1)

37. 0.009 0.012 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.003 0.003 ↑ 1.0 4 1

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

39.          

CTE contas_saldo

40. 2,477.877 3,757.162 ↓ 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,748.538..3,757.162 rows=205,443 loops=1)

41.          

Initplan (forFunction Scan)

42. 31.623 1,279.285 ↑ 1.0 1 1

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

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

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

44.          

CTE contas_total

45. 90.717 4,054.898 ↑ 2.5 205,443 1

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

  • Merge Cond: (cr_saldo.contasareceber_codigo = vcr_1.codigo)
46. 59.133 3,880.683 ↓ 205.4 205,443 1

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

  • Sort Key: cr_saldo.contasareceber_codigo
  • Sort Method: quicksort Memory: 22195kB
47. 3,821.550 3,821.550 ↓ 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,748.550..3,821.550 rows=205,443 loops=1)

48. 61.651 83.498 ↓ 2.0 205,443 1

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

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

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

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

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