explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V4Fd : Optimization for: Optimization for: plan #bIOZ; plan #ko1h

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6,013.904 6,013.904 ↑ 883.2 21 1

CTE Scan on cli_saldo (cost=98,403.06..103,503.76 rows=18,548 width=874) (actual time=6,011.658..6,013.904 rows=21 loops=1)

2.          

CTE cli

3. 284.142 294.730 ↑ 1.0 8,708 1

Hash Left Join (cost=479.73..7,655.96 rows=8,708 width=143) (actual time=4.374..294.730 rows=8,708 loops=1)

  • Hash Cond: (cl.codigo = t.codcliente)
4. 5.389 8.717 ↑ 1.0 8,708 1

Hash Left Join (cost=254.58..699.39 rows=8,708 width=122) (actual time=2.228..8.717 rows=8,708 loops=1)

  • Hash Cond: (cl.codmunicipio = m.codibge)
5. 1.121 1.121 ↑ 1.0 8,708 1

Seq Scan on clientes cl (cost=0.00..325.08 rows=8,708 width=106) (actual time=0.005..1.121 rows=8,708 loops=1)

6. 0.812 2.207 ↑ 1.0 5,571 1

Hash (cost=184.94..184.94 rows=5,571 width=20) (actual time=2.207..2.207 rows=5,571 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 356kB
7. 1.104 1.395 ↑ 1.0 5,571 1

Hash Left Join (cost=1.63..184.94 rows=5,571 width=20) (actual time=0.019..1.395 rows=5,571 loops=1)

  • Hash Cond: (m.codestado = e.codibge)
8. 0.281 0.281 ↑ 1.0 5,571 1

Seq Scan on municipios m (cost=0.00..106.71 rows=5,571 width=21) (actual time=0.003..0.281 rows=5,571 loops=1)

9. 0.005 0.010 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=7) (actual time=0.010..0.010 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.005 0.005 ↑ 1.0 28 1

Seq Scan on estados e (cost=0.00..1.28 rows=28 width=7) (actual time=0.001..0.005 rows=28 loops=1)

11. 0.856 1.871 ↑ 1.0 5,891 1

Hash (cost=151.51..151.51 rows=5,891 width=25) (actual time=1.871..1.871 rows=5,891 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 410kB
12. 1.015 1.015 ↑ 1.0 5,891 1

Seq Scan on clientes_telefones t (cost=0.00..151.51 rows=5,891 width=25) (actual time=0.003..1.015 rows=5,891 loops=1)

  • Filter: principal
  • Rows Removed by Filter: 1560
13.          

CTE cli_saldo

14. 0.054 6,010.039 ↑ 883.2 21 1

WindowAgg (cost=90,003.05..90,747.10 rows=18,548 width=822) (actual time=6,009.986..6,010.039 rows=21 loops=1)

15. 0.020 6,009.985 ↑ 883.2 21 1

Merge Join (cost=90,003.05..90,283.40 rows=18,548 width=822) (actual time=6,009.956..6,009.985 rows=21 loops=1)

  • Merge Cond: (cli_saldo_1.clientes_codigo = cl_1.codcliente)
16. 0.012 5,701.744 ↑ 20.3 21 1

Sort (cost=89,259.03..89,260.10 rows=426 width=56) (actual time=5,701.742..5,701.744 rows=21 loops=1)

  • Sort Key: cli_saldo_1.clientes_codigo
  • Sort Method: quicksort Memory: 26kB
17. 0.006 5,701.732 ↑ 20.3 21 1

Subquery Scan on cli_saldo_1 (cost=75,098.42..89,240.43 rows=426 width=56) (actual time=5,701.670..5,701.732 rows=21 loops=1)

18. 5,701.726 5,701.726 ↑ 20.3 21 1

CTE Scan on contas_total (cost=75,098.42..89,236.17 rows=426 width=56) (actual time=5,701.668..5,701.726 rows=21 loops=1)

  • Filter: ((entidade_codigo IS NOT NULL) AND (NOT paga) AND (diasvencidos > 0) AND (datavencimento >= '2019-01-01'::date) AND (datavencimento <= '2019-02-05'::date))
  • Rows Removed by Filter: 205425
19.          

CTE contas

20. 104.007 1,144.588 ↓ 2.0 205,446 1

Subquery Scan on vcontasareceber (cost=46,146.77..52,830.04 rows=102,820 width=510) (actual time=248.454..1,144.588 rows=205,446 loops=1)

  • Filter: vcontasareceber.faturada
  • Rows Removed by Filter: 193
21. 1,040.581 1,040.581 ↑ 1.0 205,639 1

CTE Scan on contas (cost=46,146.77..50,259.55 rows=205,639 width=511) (actual time=248.447..1,040.581 rows=205,639 loops=1)

22.          

CTE contas

23. 89.976 828.357 ↑ 1.0 205,639 1

Hash Left Join (cost=25,165.21..46,146.77 rows=205,639 width=129) (actual time=248.439..828.357 rows=205,639 loops=1)

  • Hash Cond: (cr.tipodocumento_codigo = t_1.codigo)
24. 37.467 738.366 ↑ 1.0 205,639 1

Hash Left Join (cost=25,163.72..43,317.74 rows=205,639 width=115) (actual time=248.418..738.366 rows=205,639 loops=1)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
25. 60.017 700.888 ↑ 1.0 205,639 1

Hash Left Join (cost=25,162.63..42,545.50 rows=205,639 width=106) (actual time=248.402..700.888 rows=205,639 loops=1)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
26. 92.801 640.855 ↑ 1.0 205,639 1

Hash Left Join (cost=25,161.49..39,716.82 rows=205,639 width=93) (actual time=248.375..640.855 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
27. 74.382 495.304 ↑ 1.0 205,639 1

Hash Left Join (cost=19,623.62..31,351.41 rows=205,639 width=89) (actual time=194.402..495.304 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
28. 81.181 366.282 ↑ 1.0 205,639 1

Hash Left Join (cost=14,672.14..23,854.80 rows=205,639 width=81) (actual time=138.621..366.282 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
29. 69.089 228.909 ↑ 1.0 205,639 1

Hash Left Join (cost=8,938.67..15,295.75 rows=205,639 width=77) (actual time=81.276..228.909 rows=205,639 loops=1)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
30. 59.926 82.008 ↑ 1.0 205,639 1

Hash Left Join (cost=694.78..6,000.08 rows=205,639 width=65) (actual time=3.291..82.008 rows=205,639 loops=1)

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

Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=57) (actual time=0.006..18.804 rows=205,639 loops=1)

32. 0.045 3.278 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
33. 0.094 3.233 ↑ 1.0 176 1

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

34. 1.811 2.963 ↑ 1.0 176 1

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

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

36. 0.032 0.050 ↑ 1.0 176 1

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

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

38. 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)
39. 6.022 77.812 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1572kB
40. 22.887 71.790 ↑ 1.0 28,064 1

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

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

42. 5.238 43.251 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
43. 10.463 38.013 ↑ 1.0 28,064 1

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

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

45. 16.922 26.088 ↑ 1.0 93,499 1

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

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

47. 37.920 56.192 ↓ 1.0 205,446 1

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

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

49. 37.235 54.640 ↑ 1.0 177,399 1

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

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

51. 41.590 52.750 ↑ 1.0 205,639 1

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

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

53. 0.013 0.016 ↑ 1.0 6 1

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

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

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

55. 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
56. 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.001..0.002 rows=4 loops=1)

57. 0.010 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
58. 0.005 0.005 ↑ 1.0 22 1

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

59.          

CTE contas_saldo

60. 3,966.327 3,966.327 ↓ 205.4 205,446 1

Function Scan on contasareceber_saldo (cost=0.26..10.26 rows=1,000 width=76) (actual time=3,937.619..3,966.327 rows=205,446 loops=1)

61.          

CTE contas_total

62. 97.091 5,538.842 ↑ 2.5 205,446 1

Merge Right Join (cost=10,685.87..22,258.12 rows=514,100 width=573) (actual time=5,427.373..5,538.842 rows=205,446 loops=1)

  • Merge Cond: (cr_saldo.contasareceber_codigo = vcr.codigo)
63. 56.159 4,098.327 ↓ 205.4 205,446 1

Sort (cost=69.83..72.33 rows=1,000 width=76) (actual time=4,091.030..4,098.327 rows=205,446 loops=1)

  • Sort Key: cr_saldo.contasareceber_codigo
  • Sort Method: quicksort Memory: 22195kB
64. 4,042.168 4,042.168 ↓ 205.4 205,446 1

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

65. 76.756 1,343.424 ↓ 2.0 205,446 1

Sort (cost=10,616.04..10,873.09 rows=102,820 width=501) (actual time=1,336.329..1,343.424 rows=205,446 loops=1)

  • Sort Key: vcr.codigo
  • Sort Method: quicksort Memory: 35036kB
66. 1,266.668 1,266.668 ↓ 2.0 205,446 1

CTE Scan on contas vcr (cost=0.00..2,056.40 rows=102,820 width=501) (actual time=248.456..1,266.668 rows=205,446 loops=1)

67. 4.567 308.221 ↑ 74.4 117 1

Sort (cost=744.02..765.79 rows=8,708 width=770) (actual time=308.211..308.221 rows=117 loops=1)

  • Sort Key: cl_1.codcliente
  • Sort Method: quicksort Memory: 2469kB
68. 303.654 303.654 ↑ 1.0 8,708 1

CTE Scan on cli cl_1 (cost=0.00..174.16 rows=8,708 width=770) (actual time=4.378..303.654 rows=8,708 loops=1)