explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jfga

Settings
# exclusive inclusive rows x rows loops node
1. 0.259 1,518.343 ↑ 9.0 304 1

Hash Anti Join (cost=60,908.61..62,745.65 rows=2,721 width=266) (actual time=1,441.947..1,518.343 rows=304 loops=1)

  • Hash Cond: (c.codigo = s.codcliente)
2. 0.181 1,518.074 ↑ 17.9 304 1

Hash Left Join (cost=60,795.91..62,540.78 rows=5,442 width=266) (actual time=1,441.920..1,518.074 rows=304 loops=1)

  • Hash Cond: (c.codigo = ultimacompra.codcliente)
3. 0.110 1,297.332 ↑ 3.6 304 1

Hash Left Join (cost=60,773.16..62,324.91 rows=1,088 width=258) (actual time=1,221.348..1,297.332 rows=304 loops=1)

  • Hash Cond: (c.codvendedor = vdd.codigo)
4. 0.130 1,297.203 ↑ 3.6 304 1

Hash Anti Join (cost=60,771.65..62,318.07 rows=1,088 width=242) (actual time=1,221.318..1,297.203 rows=304 loops=1)

  • Hash Cond: (c.codigo = vprevendas.codcliente)
5. 11.974 1,282.432 ↑ 7.1 305 1

Hash Left Join (cost=55,432.59..56,317.89 rows=2,177 width=231) (actual time=1,206.665..1,282.432 rows=305 loops=1)

  • Hash Cond: (m.codestado = e.codibge)
6.          

CTE cliente

7. 44.912 44.912 ↑ 1.0 8,708 1

Seq Scan on clientes (cost=0.00..4,766.16 rows=8,708 width=278) (actual time=0.072..44.912 rows=8,708 loops=1)

8. 0.157 1,270.441 ↑ 7.1 305 1

Hash Left Join (cost=50,664.80..50,970.48 rows=2,177 width=232) (actual time=1,206.417..1,270.441 rows=305 loops=1)

  • Hash Cond: (c.codmunicipio = m.codibge)
9. 1.193 1,268.704 ↑ 7.1 305 1

Hash Join (cost=50,488.45..50,764.19 rows=2,177 width=215) (actual time=1,204.821..1,268.704 rows=305 loops=1)

  • Hash Cond: (c.codigo = cr_ct.clientes_codigo)
10. 5.182 65.198 ↓ 3.0 8,708 1

Hash Left Join (cost=225.15..468.23 rows=2,903 width=215) (actual time=2.416..65.198 rows=8,708 loops=1)

  • Hash Cond: (c.codigo = t.codcliente)
11. 57.723 57.723 ↓ 3.0 8,708 1

CTE Scan on cliente c (cost=0.00..195.93 rows=2,903 width=194) (actual time=0.078..57.723 rows=8,708 loops=1)

  • Filter: (dtcadastro <= '2019-01-31'::date)
12. 1.099 2.293 ↑ 1.0 5,891 1

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

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

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

  • Filter: principal
  • Rows Removed by Filter: 1560
14. 0.048 1,202.313 ↓ 2.0 305 1

Hash (cost=50,261.43..50,261.43 rows=150 width=4) (actual time=1,202.313..1,202.313 rows=305 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
15. 0.027 1,202.265 ↓ 2.0 305 1

Subquery Scan on cr_ct (cost=50,258.06..50,261.43 rows=150 width=4) (actual time=1,201.495..1,202.265 rows=305 loops=1)

16. 57.328 1,202.238 ↓ 2.0 305 1

HashAggregate (cost=50,258.06..50,259.93 rows=150 width=5) (actual time=1,201.493..1,202.238 rows=305 loops=1)

  • Group Key: vcr.clientes_codigo
  • Filter: (count(*) FILTER (WHERE vcr.aceitapagamento) > 0)
  • Rows Removed by Filter: 5087
17. 50.443 1,144.910 ↓ 1.3 205,276 1

Subquery Scan on vcr (cost=43,317.74..49,486.91 rows=154,229 width=5) (actual time=253.762..1,144.910 rows=205,276 loops=1)

  • Filter: (vcr.aceitapagamento OR vcr.estornapagamento)
  • Rows Removed by Filter: 363
18. 1,094.467 1,094.467 ↑ 1.0 205,639 1

CTE Scan on contas (cost=43,317.74..47,430.52 rows=205,639 width=50) (actual time=253.760..1,094.467 rows=205,639 loops=1)

19.          

CTE contas

20. 82.817 848.983 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
21. 67.869 766.154 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
22. 103.977 698.277 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
23. 84.970 540.938 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
24. 97.099 398.295 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
25. 77.716 245.085 ↑ 1.0 205,639 1

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

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
26. 64.455 87.881 ↑ 1.0 205,639 1

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

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

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

28. 0.042 3.502 ↑ 1.0 176 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
29. 0.125 3.460 ↑ 1.0 176 1

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

30. 1.974 3.159 ↑ 1.0 176 1

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

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

32. 0.017 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
33. 0.021 0.021 ↑ 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.021 rows=176 loops=1)

34. 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)
35. 5.756 79.488 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1572kB
36. 22.193 73.732 ↑ 1.0 28,064 1

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

  • Hash Cond: (pv_1.codigo = pvpg.codprevenda)
37. 5.383 5.383 ↑ 1.0 91,560 1

Seq Scan on prevendas pv_1 (cost=0.00..3,295.60 rows=91,560 width=8) (actual time=0.001..5.383 rows=91,560 loops=1)

38. 6.040 46.156 ↑ 1.0 28,064 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1462kB
39. 10.738 40.116 ↑ 1.0 28,064 1

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

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

41. 18.354 27.836 ↑ 1.0 93,499 1

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

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

43. 37.344 56.111 ↓ 1.0 205,446 1

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

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

45. 39.096 57.673 ↑ 1.0 177,399 1

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

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

47. 42.049 53.362 ↑ 1.0 205,639 1

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

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

49. 0.005 0.008 ↑ 1.0 6 1

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

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

51. 0.010 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
52. 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)

53. 0.927 1.580 ↑ 1.0 5,571 1

Hash (cost=106.71..106.71 rows=5,571 width=21) (actual time=1.580..1.580 rows=5,571 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 362kB
54. 0.653 0.653 ↑ 1.0 5,571 1

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

55. 0.013 0.017 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
56. 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.002..0.004 rows=28 loops=1)

57. 0.016 14.641 ↑ 458.0 1 1

Hash (cost=5,333.33..5,333.33 rows=458 width=4) (actual time=14.641..14.641 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.001 14.625 ↑ 458.0 1 1

Subquery Scan on vprevendas (cost=0.29..5,333.33 rows=458 width=4) (actual time=14.576..14.625 rows=1 loops=1)

59. 0.227 14.624 ↑ 458.0 1 1

Nested Loop Left Join (cost=0.29..5,328.75 rows=458 width=26) (actual time=14.575..14.624 rows=1 loops=1)

60. 0.006 14.340 ↑ 458.0 1 1

Nested Loop (cost=0.29..5,197.70 rows=458 width=20) (actual time=14.338..14.340 rows=1 loops=1)

61. 14.325 14.325 ↑ 458.0 1 1

Seq Scan on prevendas_faturamento (cost=0.00..2,415.14 rows=458 width=4) (actual time=14.324..14.325 rows=1 loops=1)

  • Filter: ((codprevenda IS NOT NULL) AND ((datahora)::date >= '2019-01-01'::date) AND ((datahora)::date <= '2019-01-31'::date))
  • Rows Removed by Filter: 91556
62. 0.009 0.009 ↑ 1.0 1 1

Index Scan using prevendas_codigo_index on prevendas pv (cost=0.29..6.07 rows=1 width=40) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (codigo = prevendas_faturamento.codprevenda)
63. 0.005 0.057 ↑ 1.0 1 1

Materialize (cost=0.00..7.39 rows=1 width=6) (actual time=0.011..0.057 rows=1 loops=1)

64. 0.052 0.052 ↑ 1.0 1 1

Seq Scan on configuracoes (cost=0.00..7.39 rows=1 width=6) (actual time=0.007..0.052 rows=1 loops=1)

  • Filter: (parametro = 'DDIPADRAO'::text)
  • Rows Removed by Filter: 190
65. 0.013 0.019 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=20) (actual time=0.019..0.019 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
66. 0.006 0.006 ↑ 1.0 23 1

Seq Scan on vendedores vdd (cost=0.00..1.23 rows=23 width=20) (actual time=0.002..0.006 rows=23 loops=1)

67. 0.542 220.561 ↓ 3.9 3,937 1

Hash (cost=10.25..10.25 rows=1,000 width=12) (actual time=220.561..220.561 rows=3,937 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 202kB
68. 220.019 220.019 ↓ 3.9 3,937 1

Function Scan on cliente_ultimacompra ultimacompra (cost=0.25..10.25 rows=1,000 width=12) (actual time=219.646..220.019 rows=3,937 loops=1)

69. 0.000 0.010 ↓ 0.0 0 1

Hash (cost=112.58..112.58 rows=9 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
70. 0.000 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.29..112.58 rows=9 width=4) (actual time=0.010..0.010 rows=0 loops=1)

71. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on saidasnf_faturamento fat (cost=0.00..37.75 rows=9 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: ((codsaidasnf IS NOT NULL) AND (datahora >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2019-01-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 2
72. 0.000 0.000 ↓ 0.0 0

Index Scan using saidasnf_codigo_index on saidasnf s (cost=0.29..8.30 rows=1 width=24) (never executed)

  • Index Cond: (codigo = fat.codsaidasnf)