explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FI2E

Settings
# exclusive inclusive rows x rows loops node
1. 17.607 818.006 ↑ 7.3 681 1

GroupAggregate (cost=106,279.38..106,402.93 rows=4,942 width=70) (actual time=788.135..818.006 rows=681 loops=1)

  • Group Key: b.c_prod
2. 45.009 800.399 ↓ 3.1 15,105 1

Sort (cost=106,279.38..106,291.73 rows=4,942 width=17) (actual time=788.021..800.399 rows=15,105 loops=1)

  • Sort Key: b.c_prod
  • Sort Method: quicksort Memory: 1,542kB
3. 27.063 755.390 ↓ 3.1 15,105 1

Hash Join (cost=2,194.43..105,976.16 rows=4,942 width=17) (actual time=121.030..755.390 rows=15,105 loops=1)

  • Hash Cond: (b.cfop = cfop.cfop)
4. 67.904 728.008 ↓ 3.1 15,419 1

Hash Anti Join (cost=2,167.53..105,923.67 rows=4,942 width=21) (actual time=120.062..728.008 rows=15,419 loops=1)

  • Hash Cond: ((b.c_prod)::text = (inv.cod_item)::text)
5. 249.570 624.160 ↓ 6.7 53,849 1

Hash Join (cost=1,330.28..104,558.14 rows=7,997 width=21) (actual time=84.005..624.160 rows=53,849 loops=1)

  • Hash Cond: ((b.c_prod)::text = (icms.cod_item)::text)
6. 294.189 331.788 ↓ 8.4 226,692 1

Bitmap Heap Scan on i01_prod b (cost=654.64..103,743.70 rows=26,829 width=21) (actual time=41.103..331.788 rows=226,692 loops=1)

  • Recheck Cond: (((cpf_cnpj)::text = '04084183000121'::text) AND (d_emi >= '2019-01-01'::date) AND (d_emi <= '2019-12-31'::date))
  • Heap Blocks: exact=19,556
7. 37.599 37.599 ↓ 8.4 226,692 1

Bitmap Index Scan on i01_prod_cpf_cnpj_d_emi_idx (cost=0.00..647.93 rows=26,829 width=0) (actual time=37.598..37.599 rows=226,692 loops=1)

  • Index Cond: (((cpf_cnpj)::text = '04084183000121'::text) AND (d_emi >= '2019-01-01'::date) AND (d_emi <= '2019-12-31'::date))
8. 8.855 42.802 ↑ 1.2 9,151 1

Hash (cost=543.79..543.79 rows=10,548 width=6) (actual time=42.801..42.802 rows=9,151 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 474kB
9. 22.329 33.947 ↑ 1.2 9,151 1

HashAggregate (cost=438.31..543.79 rows=10,548 width=6) (actual time=24.680..33.947 rows=9,151 loops=1)

  • Group Key: (icms.cod_item)::text
10. 11.618 11.618 ↓ 1.1 11,651 1

Index Only Scan using empresa_has_substituicao_oculto_id_empresa_cod_item_idx on empresa_has_substituicao_oculto icms (cost=0.42..410.57 rows=11,096 width=6) (actual time=0.014..11.618 rows=11,651 loops=1)

  • Index Cond: (id_empresa = 12)
  • Heap Fetches: 28
11. 16.845 35.944 ↓ 1.2 16,664 1

Hash (cost=659.18..659.18 rows=14,245 width=5) (actual time=35.943..35.944 rows=16,664 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 883kB
12. 19.099 19.099 ↓ 1.2 16,664 1

Index Only Scan using inventario_iten_pkey on inventario_item inv (cost=0.42..659.18 rows=14,245 width=5) (actual time=0.023..19.099 rows=16,664 loops=1)

  • Index Cond: ((id_empresa = 12) AND (dt_inv = '2019-12-31'::date))
  • Heap Fetches: 0
13. 0.078 0.319 ↑ 1.0 89 1

Hash (cost=25.79..25.79 rows=89 width=4) (actual time=0.319..0.319 rows=89 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
14. 0.241 0.241 ↑ 1.0 89 1

Seq Scan on cad_cfop cfop (cost=0.00..25.79 rows=89 width=4) (actual time=0.010..0.241 rows=89 loops=1)

  • Filter: (id_operacao = ANY ('{1001,1005}'::integer[]))
  • Rows Removed by Filter: 534
Planning time : 2.318 ms
Execution time : 818.696 ms