explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ix6S

Settings
# exclusive inclusive rows x rows loops node
1. 21.281 288,593.257 ↓ 4,166.7 100,000 1

Limit (cost=30.82..20,669.41 rows=24 width=784) (actual time=39.114..288,593.257 rows=100,000 loops=1)

2. 282.409 288,571.976 ↓ 4,166.7 100,000 1

Nested Loop (cost=30.82..20,669.41 rows=24 width=784) (actual time=39.111..288,571.976 rows=100,000 loops=1)

3. 68.744 3,889.567 ↓ 100,000.0 100,000 1

Nested Loop (cost=7.04..593.47 rows=1 width=535) (actual time=34.662..3,889.567 rows=100,000 loops=1)

4. 110.545 3,320.823 ↓ 100,000.0 100,000 1

Nested Loop (cost=6.76..589.67 rows=1 width=537) (actual time=34.585..3,320.823 rows=100,000 loops=1)

5. 199.041 2,410.278 ↓ 100,000.0 100,000 1

Nested Loop (cost=6.33..581.22 rows=1 width=568) (actual time=34.427..2,410.278 rows=100,000 loops=1)

  • Join Filter: (((b.codigoempresa)::text = (c.codigoempresa)::text) AND (b.tipoestoque = c.tipoestoque) AND ((b.codigo)::text = (c.codigoproduto)::text))
6. 54.895 728.455 ↓ 105,913.0 105,913 1

Nested Loop (cost=5.77..575.64 rows=1 width=559) (actual time=0.649..728.455 rows=105,913 loops=1)

7. 15.188 146.424 ↓ 456.0 31,008 1

Nested Loop (cost=5.34..289.27 rows=68 width=353) (actual time=0.470..146.424 rows=31,008 loops=1)

  • Join Filter: ((d.codempresa)::text = (b.codigoempresa)::text)
8. 0.033 0.196 ↓ 36.0 36 1

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

  • Join Filter: ((d.codempresa)::text = (i.codigoempresa)::text)
9. 0.064 0.064 ↑ 1.0 1 1

Seq Scan on filial d (cost=0.00..13.53 rows=1 width=59) (actual time=0.064..0.064 rows=1 loops=1)

  • Filter: ((codfilial)::text = '61'::text)
  • Rows Removed by Filter: 6
10. 0.099 0.099 ↑ 4.6 36 1

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

11. 86.112 131.040 ↓ 7.6 861 36

Bitmap Heap Scan on produto b (cost=5.34..268.62 rows=113 width=271) (actual time=1.329..3.640 rows=861 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=5,829
12. 44.928 44.928 ↓ 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.248..1.248 rows=11,856 loops=36)

  • Index Cond: (((codigoempresa)::text = (i.codigoempresa)::text) AND ((unidademedida)::text = (i.sigla)::text))
13. 527.136 527.136 ↓ 3.0 3 31,008

Index Scan using ix_produtovariacao_codigoempresa_tipoestoque_codigoproduto on produtovariacao a (cost=0.43..4.20 rows=1 width=206) (actual time=0.013..0.017 rows=3 loops=31,008)

  • Index Cond: (((codigoempresa)::text = (b.codigoempresa)::text) AND (tipoestoque = b.tipoestoque) AND ((codigoproduto)::text = (b.codigo)::text))
14. 1,482.782 1,482.782 ↑ 1.0 1 105,913

Index Scan using ix_preco_codigoempresa_tipoestoque_codigoproduto_codigofilial on preco c (cost=0.56..5.56 rows=1 width=104) (actual time=0.014..0.014 rows=1 loops=105,913)

  • Index Cond: (((codigoempresa)::text = (a.codigoempresa)::text) AND (tipoestoque = a.tipoestoque) AND ((codigoproduto)::text = (a.codigoproduto)::text) AND ((codigofilial)::text = '61'::text))
15. 800.000 800.000 ↑ 1.0 1 100,000

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

  • Index Cond: (cep = (d.codigocep)::text)
  • Heap Fetches: 100,000
16. 500.000 500.000 ↑ 1.0 1 100,000

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

  • Index Cond: ((codigo)::text = (e.codigocidade)::text)
17. 1,200.000 284,400.000 ↑ 797.0 1 100,000

Nested Loop Left Join (cost=23.78..20,058.41 rows=797 width=94) (actual time=1.890..2.844 rows=1 loops=100,000)

  • Join Filter: (t2.trbgrpcod = COALESCE(k.systrbgrpc, x.trbgrpcod))
  • Rows Removed by Join Filter: 115
18. 100.000 3,100.000 ↑ 797.0 1 100,000

Nested Loop Left Join (cost=23.49..20,015.59 rows=797 width=92) (actual time=0.024..0.031 rows=1 loops=100,000)

19. 200.000 2,700.000 ↑ 797.0 1 100,000

Nested Loop Left Join (cost=19.02..1,058.62 rows=797 width=16) (actual time=0.020..0.027 rows=1 loops=100,000)

20. 200.000 1,500.000 ↑ 797.0 1 100,000

Nested Loop Left Join (cost=19.02..1,046.03 rows=797 width=10) (actual time=0.015..0.015 rows=1 loops=100,000)

21. 900.000 900.000 ↑ 1.0 1 100,000

Index Scan using ix_adm0182_empcod_esttip_procod_ on adm0182 x (cost=0.42..8.44 rows=1 width=2) (actual time=0.009..0.009 rows=1 loops=100,000)

  • Index Cond: (((empcod)::text = (b.codigoempresa)::text) AND (esttip = a.tipoestoque) AND ((procod)::text = (a.codigoproduto)::text))
22. 200.000 400.000 ↑ 797.0 1 100,000

Bitmap Heap Scan on sys025 y (cost=18.60..1,029.62 rows=797 width=8) (actual time=0.003..0.004 rows=1 loops=100,000)

  • Recheck Cond: (idprodutofilho = a.idproduto)
  • Heap Blocks: exact=64,163
23. 200.000 200.000 ↑ 797.0 1 100,000

Bitmap Index Scan on ix_sys025_idprodutofilho (cost=0.00..18.40 rows=797 width=0) (actual time=0.002..0.002 rows=1 loops=100,000)

  • Index Cond: (idprodutofilho = a.idproduto)
24. 100.000 1,000.000 ↑ 1.0 1 100,000

Materialize (cost=0.00..2.63 rows=1 width=6) (actual time=0.004..0.010 rows=1 loops=100,000)

25. 900.000 900.000 ↑ 1.0 1 100,000

Seq Scan on sys004 w (cost=0.00..2.62 rows=1 width=6) (actual time=0.003..0.009 rows=1 loops=100,000)

  • Filter: (((syscenufor)::text = (f.uf)::text) AND ((syscenufde)::text = (f.uf)::text) AND (sysnatopec = 121) AND (syscenina = 0) AND (sysperoric = 3) AND (sysperdesc = 6) AND (syscenicml = 1))
  • Rows Removed by Filter: 58
26. 100.000 300.000 ↑ 1.0 1 100,000

Bitmap Heap Scan on sys020 k (cost=4.47..23.78 rows=1 width=104) (actual time=0.003..0.003 rows=1 loops=100,000)

  • Recheck Cond: ((idproduto = y.idproduto) AND (idcenario = w.id))
  • Filter: (((syscenlibi = 1) AND (w.syscenicml = 0)) OR (w.syscenicml = 1))
  • Heap Blocks: exact=64,161
27. 200.000 200.000 ↑ 5.0 1 100,000

Bitmap Index Scan on ix_sys020_idproduto_idcenario_syscenlibi (cost=0.00..4.47 rows=5 width=0) (actual time=0.002..0.002 rows=1 loops=100,000)

  • Index Cond: ((idproduto = y.idproduto) AND (idcenario = w.id))
28. 2,000.000 280,100.000 ↓ 116.0 116 100,000

Materialize (cost=0.29..10.94 rows=1 width=20) (actual time=0.021..2.801 rows=116 loops=100,000)

29. 278,100.000 278,100.000 ↓ 116.0 116 100,000

Index Scan using ix_adm0181_codigoempresa_trbgrpcod_trbgrpufor_trbgrpufde on adm0181 t2 (cost=0.29..10.94 rows=1 width=20) (actual time=0.020..2.781 rows=116 loops=100,000)

  • Index Cond: (((codigoempresa)::text = (b.codigoempresa)::text) AND ((trbgrpufor)::text = (f.uf)::text) AND ((trbgrpufde)::text = (f.uf)::text))
Planning time : 99.978 ms
Execution time : 288,606.417 ms