explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bIOZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,906.154 5,906.154 ↑ 174.2 5 1

CTE Scan on cli_saldo (cost=98,576.56..98,816.08 rows=871 width=874) (actual time=5,905.591..5,906.154 rows=5 loops=1)

2.          

CTE cli

3. 285.825 296.601 ↑ 1.0 8,708 1

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

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

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

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

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

6. 0.946 2.470 ↑ 1.0 5,571 1

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

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

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

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

9. 0.005 0.009 ↑ 1.0 28 1

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

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

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

11. 0.969 1.999 ↑ 1.0 5,891 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 410kB
12. 1.030 1.030 ↑ 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.030 rows=5,891 loops=1)

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

CTE cli_saldo

14. 0.064 5,903.139 ↑ 174.2 5 1

HashAggregate (cost=90,903.18..90,920.60 rows=871 width=822) (actual time=5,903.131..5,903.139 rows=5 loops=1)

  • Group Key: cl_1.codcliente, cl_1.nome, cl_1.endereco_formatado, cl_1.bairro, cl_1.cepformatado, cl_1.cpfcnpjformatado, cl_1.rgie, cl_1.codmunicipio, cl_1.cidade, cl_1.estado, cl_1.apelido, cl_1.telefone, cl_1.foneformatado
15. 1.990 5,903.075 ↑ 883.2 21 1

Hash Join (cost=89,265.42..90,114.89 rows=18,548 width=822) (actual time=5,718.377..5,903.075 rows=21 loops=1)

  • Hash Cond: (cl_1.codcliente = cli_saldo_1.clientes_codigo)
16. 305.713 305.713 ↑ 1.0 8,708 1

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

17. 0.010 5,595.372 ↑ 20.3 21 1

Hash (cost=89,260.10..89,260.10 rows=426 width=56) (actual time=5,595.372..5,595.372 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.005 5,595.362 ↑ 20.3 21 1

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

19. 0.019 5,595.357 ↑ 20.3 21 1

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

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

CTE contas

21. 104.150 1,100.412 ↓ 2.0 205,446 1

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

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

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

23.          

CTE contas

24. 92.269 785.671 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.tipodocumento_codigo = t_1.codigo)
25. 40.497 693.385 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
26. 58.234 652.877 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
27. 88.798 594.629 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
28. 72.783 461.121 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
29. 79.634 338.392 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
30. 58.912 205.061 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
31. 56.424 77.616 ↑ 1.0 205,639 1

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

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

33. 0.030 3.153 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
34. 0.105 3.123 ↑ 1.0 176 1

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

35. 1.776 2.842 ↑ 1.0 176 1

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

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

37. 0.022 0.037 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
38. 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.003..0.015 rows=176 loops=1)

39. 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)
40. 5.573 68.533 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1572kB
41. 20.298 62.960 ↑ 1.0 28,064 1

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

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

43. 4.534 37.364 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
44. 8.459 32.830 ↑ 1.0 28,064 1

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

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

46. 14.335 23.042 ↑ 1.0 93,499 1

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

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

48. 35.618 53.697 ↓ 1.0 205,446 1

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

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

50. 32.704 49.946 ↑ 1.0 177,399 1

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

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

52. 33.221 44.710 ↑ 1.0 205,639 1

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

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

54. 0.010 0.014 ↑ 1.0 6 1

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

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

56. 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
57. 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)

58. 0.012 0.017 ↑ 1.0 22 1

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

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

60.          

CTE contas_saldo

61. 3,912.966 3,912.966 ↓ 205.4 205,446 1

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

62.          

CTE contas_total

63. 95.816 5,433.404 ↑ 2.5 205,446 1

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

  • Merge Cond: (cr_saldo.contasareceber_codigo = vcr.codigo)
64. 54.351 4,039.914 ↓ 205.4 205,446 1

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

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

66. 75.218 1,297.674 ↓ 2.0 205,446 1

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

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

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

68. 5,595.338 5,595.338 ↑ 20.3 21 1

CTE Scan on contas_total (cost=0.00..14,137.75 rows=426 width=178) (actual time=5,595.281..5,595.338 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