explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HHH5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 422.274 8,574.165 ↓ 300,000.0 300,000 1

Limit (cost=7.04..593.51 rows=1 width=680) (actual time=19.787..8,574.165 rows=300,000 loops=1)

2. 102.660 6,951.891 ↓ 300,000.0 300,000 1

Nested Loop (cost=6.76..589.67 rows=1 width=507) (actual time=19.742..6,951.891 rows=300,000 loops=1)

3. 363.900 4,749.231 ↓ 300,000.0 300,000 1

Nested Loop (cost=6.33..581.22 rows=1 width=538) (actual time=19.711..4,749.231 rows=300,000 loops=1)

  • Join Filter: (((b.codigoempresa)::text = (c.codigoempresa)::text) AND (b.tipoestoque = c.tipoestoque) AND ((b.codigo)::text = (c.codigoproduto)::text))
4. 73.332 1,015.118 ↓ 306,383.0 306,383 1

Nested Loop (cost=5.77..575.64 rows=1 width=551) (actual time=0.212..1,015.118 rows=306,383 loops=1)

5. 20.026 135.253 ↓ 912.4 62,041 1

Nested Loop (cost=5.34..289.27 rows=68 width=353) (actual time=0.171..135.253 rows=62,041 loops=1)

  • Join Filter: ((d.codempresa)::text = (b.codigoempresa)::text)
6. 0.042 0.063 ↓ 36.0 36 1

Nested Loop (cost=0.00..19.24 rows=1 width=82) (actual time=0.031..0.063 rows=36 loops=1)

  • Join Filter: ((d.codempresa)::text = (i.codigoempresa)::text)
  • -> Seq Scan on filial d (cost=0.00..13.53 rows=1 width=59) (actual time=0.015..0.015 rows=1 loops=1) Filter: ((codfilial)::text = '61'::text)
  • Rows Removed by Filter: 6
7. 0.021 0.021 ↑ 4.6 36 1

Seq Scan on unidademedida i (cost=0.00..3.65 rows=165 width=23) (actual time=0.010..0.021 rows=36 loops=1)

8. 69.624 115.164 ↓ 15.2 1,723 36

Bitmap Heap Scan on produto b (cost=5.34..268.62 rows=113 width=271) (actual time=1.348..3.199 rows=1,723 loops=36)

  • Recheck Cond: (((codigoempresa)::text = (i.codigoempresa)::text) AND ((unidademedida)::text = (i.sigla)::text))
  • Filter: ((idunidademedida IS NOT NULL) AND (idfamiliaproduto IS NOT NULL) AND (idclassificacaofiscal IS NOT NULL) AND ((nomeecf)::text <> ''::text))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=6,806
9. 45.540 45.540 ↓ 104.9 11,856 36

Bitmap Index Scan on ix_produto_codigoempresa_unidademedida (cost=0.00..5.31 rows=113 width=0) (actual time=1.265..1.265 rows=11,856 loops=36)

  • Index Cond: (((codigoempresa)::text = (i.codigoempresa)::text) AND ((unidademedida)::text = (i.sigla)::text))
10. 806.533 806.533 ↓ 5.0 5 62,041

Index Scan using ix_produtovariacao_codigoempresa_tipoestoque_codigoproduto on produtovariacao a (cost=0.43..4.20 rows=1 width=198) (actual time=0.011..0.013 rows=5 loops=62,041)

  • Index Cond: (((codigoempresa)::text = (b.codigoempresa)::text) AND (tipoestoque = b.tipoestoque) AND ((codigoproduto)::text = (b.codigo)::text))
11. 3,370.213 3,370.213 ↑ 1.0 1 306,383

Index Scan using ix_preco_codigoempresa_tipoestoque_codigoproduto_codigofilial on preco c (cost=0.56..5.56 rows=1 width=104) (actual time=0.011..0.011 rows=1 loops=306,383)

  • Index Cond: (((codigoempresa)::text = (a.codigoempresa)::text) AND (tipoestoque = a.tipoestoque) AND ((codigoproduto)::text = (a.codigoproduto)::text) AND ((codigofilial)::text = '61'::text))
12. 2,100.000 2,100.000 ↑ 1.0 1 300,000

Index Only Scan using ix_cep_cep_codigocidade on cep e (cost=0.43..8.45 rows=1 width=15) (actual time=0.007..0.007 rows=1 loops=300,000)

  • Index Cond: (cep = (d.codigocep)::text)
  • Heap Fetches: 300,000
13. 1,200.000 1,200.000 ↑ 1.0 1 300,000

Index Scan using ix_cidade_codigo on cidade f (cost=0.28..3.79 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=300,000)

  • Index Cond: ((codigo)::text = (e.codigocidade)::text)
Planning time : 22.867 ms
Execution time : 8,591.221 ms