explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ko1h : Optimization for: plan #bIOZ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,902.583 5,902.583 ↑ 883.2 21 1

CTE Scan on cli_saldo (cost=98,422.73..103,523.43 rows=18,548 width=874) (actual time=5,900.247..5,902.583 rows=21 loops=1)

2.          

CTE cli

3. 288.285 299.168 ↑ 1.0 8,708 1

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

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

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

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

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

6. 0.874 2.445 ↑ 1.0 5,571 1

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

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

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

  • Hash Cond: (m.codestado = e.codibge)
8. 0.293 0.293 ↑ 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.293 rows=5,571 loops=1)

9. 0.006 0.011 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=7) (actual time=0.011..0.011 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.002..0.005 rows=28 loops=1)

11. 0.824 1.916 ↑ 1.0 5,891 1

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

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

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

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

CTE cli_saldo

14. 0.053 5,898.576 ↑ 883.2 21 1

WindowAgg (cost=90,022.72..90,766.77 rows=18,548 width=822) (actual time=5,898.520..5,898.576 rows=21 loops=1)

15. 0.020 5,898.523 ↑ 883.2 21 1

Merge Join (cost=90,022.72..90,303.07 rows=18,548 width=822) (actual time=5,898.492..5,898.523 rows=21 loops=1)

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

Sort (cost=89,278.70..89,279.77 rows=426 width=56) (actual time=5,585.822..5,585.825 rows=21 loops=1)

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

Subquery Scan on cli_saldo_1 (cost=89,254.77..89,260.10 rows=426 width=56) (actual time=5,585.805..5,585.808 rows=21 loops=1)

18. 0.023 5,585.805 ↑ 20.3 21 1

Sort (cost=89,254.77..89,255.84 rows=426 width=178) (actual time=5,585.804..5,585.805 rows=21 loops=1)

  • Sort Key: contas_total.datavencimento DESC, contas_total.situacao
  • Sort Method: quicksort Memory: 26kB
19.          

CTE contas

20. 103.494 1,086.919 ↓ 2.0 205,446 1

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

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

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

22.          

CTE contas

23. 89.092 774.664 ↑ 1.0 205,639 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
31. 18.221 18.221 ↑ 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.221 rows=205,639 loops=1)

32. 0.032 3.151 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
33. 0.102 3.119 ↑ 1.0 176 1

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

34. 1.785 2.841 ↑ 1.0 176 1

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

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

36. 0.019 0.038 ↑ 1.0 176 1

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

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

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. 5.647 70.795 ↑ 1.0 28,064 1

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

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

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

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

42. 4.626 39.621 ↑ 1.0 28,064 1

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

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

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

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

45. 15.789 24.779 ↑ 1.0 93,499 1

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

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

47. 33.801 51.828 ↓ 1.0 205,446 1

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

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

49. 31.884 49.242 ↑ 1.0 177,399 1

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

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

51. 34.531 45.727 ↑ 1.0 205,639 1

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

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

53. 0.009 0.013 ↑ 1.0 6 1

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

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

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.013 0.016 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
58. 0.003 0.003 ↑ 1.0 22 1

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

59.          

CTE contas_saldo

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

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

61.          

CTE contas_total

62. 95.920 5,423.712 ↑ 2.5 205,446 1

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

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

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

  • Sort Key: cr_saldo.contasareceber_codigo
  • Sort Method: quicksort Memory: 22195kB
64. 3,985.854 3,985.854 ↓ 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,879.430..3,985.854 rows=205,446 loops=1)

65. 75.123 1,284.378 ↓ 2.0 205,446 1

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

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

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

67. 5,585.782 5,585.782 ↑ 20.3 21 1

CTE Scan on contas_total (cost=0.00..14,137.75 rows=426 width=178) (actual time=5,585.724..5,585.782 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
68. 4.476 312.678 ↑ 74.4 117 1

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

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

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