explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39Sy

Settings
# exclusive inclusive rows x rows loops node
1. 1.364 22.092 ↑ 4.0 1 1

Nested Loop (cost=498.70..13,128.46 rows=4 width=612) (actual time=22.086..22.092 rows=1 loops=1)

2. 0.009 0.860 ↑ 12.0 1 1

Merge Join (cost=498.14..504.47 rows=12 width=342) (actual time=0.855..0.860 rows=1 loops=1)

  • Merge Cond: (h.data_solicitacao = (last_day((day.day)::date)))
3. 0.012 0.796 ↑ 1.8 13 1

Sort (cost=153.19..153.25 rows=24 width=338) (actual time=0.794..0.796 rows=13 loops=1)

  • Sort Key: h.data_solicitacao
  • Sort Method: quicksort Memory: 31kB
4. 0.005 0.784 ↑ 1.8 13 1

Nested Loop (cost=61.01..152.64 rows=24 width=338) (actual time=0.778..0.784 rows=13 loops=1)

5. 0.007 0.729 ↑ 3.0 1 1

Nested Loop (cost=60.58..63.20 rows=3 width=306) (actual time=0.728..0.729 rows=1 loops=1)

  • Join Filter: ((lote.nivel_servico)::text = (n.descricao_nivel_servico)::text)
  • Rows Removed by Join Filter: 5
6. 0.008 0.008 ↑ 1.0 6 1

Seq Scan on nivel_servico n (cost=0.00..1.06 rows=6 width=22) (actual time=0.007..0.008 rows=6 loops=1)

7. 0.001 0.714 ↑ 3.0 1 6

Materialize (cost=60.58..61.88 rows=3 width=297) (actual time=0.119..0.119 rows=1 loops=6)

8. 0.007 0.713 ↑ 3.0 1 1

Hash Join (cost=60.58..61.86 rows=3 width=297) (actual time=0.713..0.713 rows=1 loops=1)

  • Hash Cond: (c.id = lote.idcomprador)
9. 0.008 0.008 ↑ 1.0 18 1

Seq Scan on comprador c (cost=0.00..1.18 rows=18 width=34) (actual time=0.006..0.008 rows=18 loops=1)

10. 0.002 0.698 ↑ 3.0 1 1

Hash (cost=60.55..60.55 rows=3 width=279) (actual time=0.697..0.698 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.002 0.696 ↑ 3.0 1 1

Nested Loop (cost=32.21..60.55 rows=3 width=279) (actual time=0.684..0.696 rows=1 loops=1)

12. 0.017 0.687 ↑ 3.0 1 1

Hash Join (cost=31.93..35.63 rows=3 width=247) (actual time=0.676..0.687 rows=1 loops=1)

  • Hash Cond: (familia_produtos.idfamilia_produto = lote.idfamilia_produto)
13. 0.060 0.060 ↑ 1.0 140 1

Seq Scan on familia_produtos (cost=0.00..3.15 rows=140 width=23) (actual time=0.010..0.060 rows=140 loops=1)

  • Filter: (((descricao_familia_produto)::text !~~ '%FEIRAO%'::text) AND ((descricao_familia_produto)::text !~~ '%AVARIA%'::text))
  • Rows Removed by Filter: 3
14. 0.002 0.610 ↑ 3.0 1 1

Hash (cost=31.89..31.89 rows=3 width=232) (actual time=0.610..0.610 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.001 0.608 ↑ 3.0 1 1

Subquery Scan on lote (cost=25.56..31.89 rows=3 width=232) (actual time=0.607..0.608 rows=1 loops=1)

16. 0.028 0.607 ↑ 3.0 1 1

Subquery Scan on a (cost=25.56..31.86 rows=3 width=1,158) (actual time=0.606..0.607 rows=1 loops=1)

17. 0.482 0.579 ↑ 3.0 1 1

Result (cost=25.56..30.92 rows=3 width=932) (actual time=0.578..0.579 rows=1 loops=1)

18. 0.006 0.097 ↑ 3.0 1 1

Sort (cost=25.56..25.56 rows=3 width=804) (actual time=0.097..0.097 rows=1 loops=1)

  • Sort Key: ((p.idproduto)::numeric), g.id_grupo
  • Sort Method: quicksort Memory: 25kB
19. 0.024 0.091 ↑ 3.0 1 1

GroupAggregate (cost=25.17..25.53 rows=3 width=804) (actual time=0.091..0.091 rows=1 loops=1)

  • Group Key: g.id_grupo, p.idproduto, p.descricao_produto, p.idcomprador, p.idfornecedor, p.idfamilia_produto, p.revenda, p.status, p.unidade_compra, p.embalagem, p.idunidade_medida, p.ressuprimento_manual, p.ressuprimento_manual_dias, p.cod_produto, p.codigo_barras
20. 0.017 0.067 ↑ 1.0 3 1

Sort (cost=25.17..25.18 rows=3 width=208) (actual time=0.066..0.067 rows=3 loops=1)

  • Sort Key: g.id_grupo, p.descricao_produto, p.idcomprador, p.idfornecedor, p.idfamilia_produto, p.status, p.unidade_compra, p.embalagem, p.idunidade_medida, p.ressuprimento_manual, p.ressuprimento_manual_dias, p.cod_produto, p.codigo_barras
  • Sort Method: quicksort Memory: 25kB
21. 0.004 0.050 ↑ 1.0 3 1

Nested Loop (cost=4.33..25.15 rows=3 width=208) (actual time=0.046..0.050 rows=3 loops=1)

  • Join Filter: (p.filial = g.filial)
  • Rows Removed by Join Filter: 12
22. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on grupo_filial g (cost=0.00..1.05 rows=5 width=16) (actual time=0.003..0.006 rows=5 loops=1)

23. 0.005 0.040 ↑ 1.0 3 5

Materialize (cost=4.33..23.88 rows=3 width=204) (actual time=0.006..0.008 rows=3 loops=5)

24. 0.021 0.035 ↑ 1.0 3 1

Bitmap Heap Scan on produtos_filial p (cost=4.33..23.87 rows=3 width=204) (actual time=0.030..0.035 rows=3 loops=1)

  • Recheck Cond: ((idproduto)::text = '2373'::text)
  • Filter: (((status)::text <> 'FL'::text) AND ((revenda)::text = 'S'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=8
25. 0.014 0.014 ↓ 3.6 18 1

Bitmap Index Scan on produtos_filial_idproduto_idx (cost=0.00..4.33 rows=5 width=0) (actual time=0.014..0.014 rows=18 loops=1)

  • Index Cond: ((idproduto)::text = '2373'::text)
26. 0.007 0.007 ↑ 1.0 1 1

Index Scan using fornecedor_pkey on fornecedor f (cost=0.28..8.29 rows=1 width=40) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (id = lote.idfornecedor)
27. 0.050 0.050 ↑ 1.0 13 1

Index Scan using hist_analise_compras_grupo_id_grupo_idx on hist_analise_compras_grupo h (cost=0.42..29.68 rows=13 width=32) (actual time=0.047..0.050 rows=13 loops=1)

  • Index Cond: ((id_grupo = lote.id_grupo) AND ((idproduto)::text = '2373'::text))
28. 0.004 0.055 ↑ 500.5 2 1

Sort (cost=344.96..347.46 rows=1,001 width=4) (actual time=0.053..0.055 rows=2 loops=1)

  • Sort Key: (last_day((day.day)::date))
  • Sort Method: quicksort Memory: 25kB
29. 0.005 0.051 ↑ 333.7 3 1

HashAggregate (cost=275.05..285.06 rows=1,001 width=4) (actual time=0.049..0.051 rows=3 loops=1)

  • Group Key: (last_day((day.day)::date))
30. 0.002 0.046 ↑ 333.7 3 1

Append (cost=0.02..272.55 rows=1,001 width=4) (actual time=0.033..0.046 rows=3 loops=1)

31. 0.042 0.042 ↑ 500.0 2 1

Function Scan on generate_series day (cost=0.02..262.52 rows=1,000 width=4) (actual time=0.033..0.042 rows=2 loops=1)

32. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

33. 0.061 0.089 ↑ 1.0 1 1

Index Scan using saldo_grupos_id_grupo_idx on saldo_grupos s (cost=0.56..26.56 rows=1 width=36) (actual time=0.088..0.089 rows=1 loops=1)

  • Index Cond: ((id_grupo = h.id_grupo) AND ((idproduto)::text = '2373'::text) AND (data = h.data_solicitacao))
  • Filter: ((h.data_solicitacao - (SubPlan 13)) > 180)
34.          

SubPlan (for Index Scan)

35. 0.002 0.028 ↑ 1.0 1 1

Aggregate (cost=17.96..17.97 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)

36. 0.004 0.026 ↓ 1.5 3 1

Nested Loop (cost=0.42..17.96 rows=2 width=4) (actual time=0.013..0.026 rows=3 loops=1)

37. 0.004 0.004 ↓ 1.5 3 1

Seq Scan on grupo_filial gf_4 (cost=0.00..1.06 rows=2 width=8) (actual time=0.004..0.004 rows=3 loops=1)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
38. 0.018 0.018 ↑ 1.0 1 3

Index Scan using prod_fil_pkey on produtos_filial pf_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)

  • Index Cond: ((filial = gf_4.filial) AND ((idproduto)::text = (s.idproduto)::text))
39.          

SubPlan (for Nested Loop)

40. 0.001 0.211 ↑ 1.0 1 1

Limit (cost=21.37..21.38 rows=1 width=4) (actual time=0.211..0.211 rows=1 loops=1)

41. 0.005 0.210 ↑ 1.0 1 1

Aggregate (cost=21.37..21.38 rows=1 width=4) (actual time=0.210..0.210 rows=1 loops=1)

42. 0.028 0.205 ↓ 12.0 12 1

Nested Loop (cost=4.32..21.37 rows=1 width=4) (actual time=0.034..0.205 rows=12 loops=1)

  • Join Filter: (em.idfilial = grupo_filial.filial)
  • Rows Removed by Join Filter: 30
43. 0.139 0.149 ↓ 14.0 14 1

Bitmap Heap Scan on entrada_mercadorias em (cost=4.32..20.28 rows=1 width=8) (actual time=0.026..0.149 rows=14 loops=1)

  • Recheck Cond: ((idproduto)::text = (lote.idproduto)::text)
  • Filter: (data_entrada <= last_day(s.data))
  • Heap Blocks: exact=12
44. 0.010 0.010 ↓ 3.5 14 1

Bitmap Index Scan on entrada_mercadorias_index02 (cost=0.00..4.32 rows=4 width=0) (actual time=0.010..0.010 rows=14 loops=1)

  • Index Cond: ((idproduto)::text = (lote.idproduto)::text)
45. 0.028 0.028 ↓ 1.5 3 14

Seq Scan on grupo_filial (cost=0.00..1.06 rows=2 width=8) (actual time=0.001..0.002 rows=3 loops=14)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
46. 0.001 0.186 ↑ 1.0 1 1

Limit (cost=21.69..21.70 rows=1 width=4) (actual time=0.186..0.186 rows=1 loops=1)

47. 0.003 0.185 ↑ 1.0 1 1

Aggregate (cost=21.69..21.70 rows=1 width=4) (actual time=0.185..0.185 rows=1 loops=1)

48. 0.022 0.182 ↓ 11.0 11 1

Nested Loop (cost=0.29..21.68 rows=1 width=4) (actual time=0.026..0.182 rows=11 loops=1)

  • Join Filter: (analise_requisicoes.filial = grupo_filial_1.filial)
  • Rows Removed by Join Filter: 28
49. 0.134 0.134 ↓ 13.0 13 1

Index Scan using analise_requisicoes_index00 on analise_requisicoes (cost=0.29..20.60 rows=1 width=8) (actual time=0.020..0.134 rows=13 loops=1)

  • Index Cond: ((idproduto)::text = (s.idproduto)::text)
  • Filter: (data_solicitacao <= last_day(s.data))
50. 0.026 0.026 ↓ 1.5 3 13

Seq Scan on grupo_filial grupo_filial_1 (cost=0.00..1.06 rows=2 width=8) (actual time=0.001..0.002 rows=3 loops=13)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
51. 0.002 0.173 ↑ 1.0 1 1

Limit (cost=21.69..21.70 rows=1 width=8) (actual time=0.173..0.173 rows=1 loops=1)

52. 0.003 0.171 ↑ 1.0 1 1

Aggregate (cost=21.69..21.70 rows=1 width=8) (actual time=0.171..0.171 rows=1 loops=1)

53. 0.019 0.168 ↓ 11.0 11 1

Nested Loop (cost=0.29..21.68 rows=1 width=8) (actual time=0.020..0.168 rows=11 loops=1)

  • Join Filter: (analise_requisicoes_1.filial = grupo_filial_2.filial)
  • Rows Removed by Join Filter: 28
54. 0.123 0.123 ↓ 13.0 13 1

Index Scan using analise_requisicoes_index00 on analise_requisicoes analise_requisicoes_1 (cost=0.29..20.60 rows=1 width=12) (actual time=0.015..0.123 rows=13 loops=1)

  • Index Cond: ((idproduto)::text = (s.idproduto)::text)
  • Filter: (data_solicitacao <= last_day(s.data))
55. 0.026 0.026 ↓ 1.5 3 13

Seq Scan on grupo_filial grupo_filial_2 (cost=0.00..1.06 rows=2 width=8) (actual time=0.001..0.002 rows=3 loops=13)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
56. 0.000 0.030 ↑ 1.0 1 1

Index Scan using saldo_grupos_id_grupo_idx on saldo_grupos (cost=22.26..30.28 rows=1 width=3) (actual time=0.029..0.030 rows=1 loops=1)

  • Index Cond: ((id_grupo = s.id_grupo) AND ((idproduto)::text = (s.idproduto)::text) AND (data = $12))
57.          

Initplan (for Index Scan)

58. 0.000 0.170 ↑ 1.0 1 1

Limit (cost=21.69..21.70 rows=1 width=4) (actual time=0.170..0.170 rows=1 loops=1)

59. 0.004 0.170 ↑ 1.0 1 1

Aggregate (cost=21.69..21.70 rows=1 width=4) (actual time=0.169..0.170 rows=1 loops=1)

60. 0.020 0.166 ↓ 11.0 11 1

Nested Loop (cost=0.29..21.68 rows=1 width=4) (actual time=0.019..0.166 rows=11 loops=1)

  • Join Filter: (analise_requisicoes_2.filial = gf.filial)
  • Rows Removed by Join Filter: 28
61. 0.120 0.120 ↓ 13.0 13 1

Index Scan using analise_requisicoes_index00 on analise_requisicoes analise_requisicoes_2 (cost=0.29..20.60 rows=1 width=8) (actual time=0.014..0.120 rows=13 loops=1)

  • Index Cond: ((idproduto)::text = (s.idproduto)::text)
  • Filter: (data_solicitacao <= last_day(s.data))
62. 0.026 0.026 ↓ 1.5 3 13

Seq Scan on grupo_filial gf (cost=0.00..1.06 rows=2 width=8) (actual time=0.001..0.002 rows=3 loops=13)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
63. 0.000 2.100 ↑ 1.0 1 1

Limit (cost=20.61..741.33 rows=1 width=28) (actual time=2.100..2.100 rows=1 loops=1)

64.          

Initplan (for Limit)

65. 0.001 0.130 ↑ 1.0 1 1

Limit (cost=20.60..20.61 rows=1 width=8) (actual time=0.130..0.130 rows=1 loops=1)

66. 0.005 0.129 ↑ 1.0 1 1

Aggregate (cost=20.60..20.61 rows=1 width=8) (actual time=0.129..0.129 rows=1 loops=1)

67. 0.124 0.124 ↓ 13.0 13 1

Index Scan using analise_requisicoes_index00 on analise_requisicoes analise_requisicoes_3 (cost=0.29..20.60 rows=1 width=8) (actual time=0.033..0.124 rows=13 loops=1)

  • Index Cond: ((idproduto)::text = (s.idproduto)::text)
  • Filter: (data_solicitacao <= last_day(s.data))
68. 2.100 2.100 ↑ 1.0 1 1

Seq Scan on analise_percepcao_compras_grupos w (cost=0.00..720.72 rows=1 width=28) (actual time=2.100..2.100 rows=1 loops=1)

  • Filter: ((id_grupo = s.id_grupo) AND ((idproduto)::text = (s.idproduto)::text) AND (ordem_compra = $15))
  • Rows Removed by Filter: 8181
69. 0.002 0.218 ↑ 1.0 1 1

Aggregate (cost=881.76..881.77 rows=1 width=8) (actual time=0.218..0.218 rows=1 loops=1)

70. 0.216 0.216 ↓ 0.0 0 1

Index Scan using saldo_grupos_id_grupo_idx on saldo_grupos saldo_grupos_1 (cost=0.56..881.74 rows=7 width=0) (actual time=0.216..0.216 rows=0 loops=1)

  • Index Cond: ((id_grupo = s.id_grupo) AND ((idproduto)::text = (s.idproduto)::text))
  • Filter: ((media_trimestre > '0'::numeric) AND (estoque = '0'::numeric) AND (data >= first_day(s.data)) AND (data <= last_day(s.data)))
  • Rows Removed by Filter: 387
71. 0.004 0.034 ↑ 1.0 1 1

Aggregate (cost=17.96..17.97 rows=1 width=4) (actual time=0.033..0.034 rows=1 loops=1)

72. 0.007 0.030 ↓ 1.5 3 1

Nested Loop (cost=0.42..17.96 rows=2 width=4) (actual time=0.016..0.030 rows=3 loops=1)

73. 0.005 0.005 ↓ 1.5 3 1

Seq Scan on grupo_filial gf_1 (cost=0.00..1.06 rows=2 width=8) (actual time=0.004..0.005 rows=3 loops=1)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
74. 0.018 0.018 ↑ 1.0 1 3

Index Scan using prod_fil_pkey on produtos_filial pf (cost=0.42..8.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3)

  • Index Cond: ((filial = gf_1.filial) AND ((idproduto)::text = (s.idproduto)::text))
75. 0.015 2.785 ↑ 1.0 1 1

Aggregate (cost=879.94..879.95 rows=1 width=32) (actual time=2.785..2.785 rows=1 loops=1)

76. 2.770 2.770 ↑ 1.4 29 1

Index Scan using saldo_grupos_id_grupo_idx on saldo_grupos s2 (cost=0.56..879.83 rows=42 width=3) (actual time=2.155..2.770 rows=29 loops=1)

  • Index Cond: ((id_grupo = s.id_grupo) AND ((idproduto)::text = (s.idproduto)::text))
  • Filter: ((data >= first_day(s.data)) AND (data <= last_day(s.data)))
  • Rows Removed by Filter: 358
77. 0.897 7.280 ↑ 1.0 1 1

Aggregate (cost=229.04..229.05 rows=1 width=4) (actual time=7.280..7.280 rows=1 loops=1)

78. 1.655 6.383 ↓ 132.6 11,673 1

Nested Loop (cost=0.43..228.82 rows=88 width=4) (actual time=0.025..6.383 rows=11,673 loops=1)

79. 0.006 0.006 ↓ 1.5 3 1

Seq Scan on grupo_filial gf_2 (cost=0.00..1.06 rows=2 width=8) (actual time=0.004..0.006 rows=3 loops=1)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
80. 4.722 4.722 ↓ 88.4 3,891 3

Index Scan using consumos_index04 on consumos c2 (cost=0.43..113.44 rows=44 width=12) (actual time=0.013..1.574 rows=3,891 loops=3)

  • Index Cond: ((filial = gf_2.filial) AND ((idproduto)::text = (lote.idproduto)::text))
81. 0.938 6.762 ↑ 1.0 1 1

Aggregate (cost=229.04..229.05 rows=1 width=4) (actual time=6.761..6.762 rows=1 loops=1)

82. 1.742 5.824 ↓ 132.6 11,673 1

Nested Loop (cost=0.43..228.82 rows=88 width=4) (actual time=0.028..5.824 rows=11,673 loops=1)

83. 0.008 0.008 ↓ 1.5 3 1

Seq Scan on grupo_filial gf_3 (cost=0.00..1.06 rows=2 width=8) (actual time=0.006..0.008 rows=3 loops=1)

  • Filter: (id_grupo = s.id_grupo)
  • Rows Removed by Filter: 2
84. 4.074 4.074 ↓ 88.4 3,891 3

Index Scan using consumos_index04 on consumos c2_1 (cost=0.43..113.44 rows=44 width=12) (actual time=0.011..1.358 rows=3,891 loops=3)

  • Index Cond: ((filial = gf_3.filial) AND ((idproduto)::text = (lote.idproduto)::text))
Planning time : 7.146 ms
Execution time : 22.622 ms