explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7S5y : Optimization for: Optimization for: plan #Zvaq; plan #ZdGn

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 48.579 1,694.588 ↓ 1.2 36,180 1

Sort (cost=72,758.07..72,833.88 rows=30,322 width=1,033) (actual time=1,689.878..1,694.588 rows=36,180 loops=1)

  • Sort Key: (CASE WHEN (c100_0150.num_doc = ''::bpchar) THEN 0 ELSE (c100_0150.num_doc)::integer END)
  • Sort Method: quicksort Memory: 20192kB
2. 13.494 1,646.009 ↓ 1.2 36,180 1

Subquery Scan on c100_0150 (cost=69,363.82..70,500.89 rows=30,322 width=1,033) (actual time=1,598.437..1,646.009 rows=36,180 loops=1)

3. 125.132 1,632.515 ↓ 1.2 36,180 1

HashAggregate (cost=69,363.82..69,970.26 rows=30,322 width=1,029) (actual time=1,598.429..1,632.515 rows=36,180 loops=1)

  • Group Key: ((a_7.id)::text), (formata_intervalo_data((""substring""((b_6.dt_in)::text, 5, 4) || ""substring""((b_6.dt_in)::text, 3, 2)))), a_7.num_doc, (((a_7.ind_oper)::text || CASE WHEN (b_5.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_5.descricao)::text) END)), (((a_7.ind_emit)::text || CASE WHEN (b_4.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_4.descricao)::text) END)), (CASE WHEN ((b_7.cod_part IS NOT NULL) AND (b_7.nome IS NOT NULL)) THEN (((b_7.cod_part)::text || ' - '::text) || (b_7.nome)::text) ELSE CASE WHEN ((b_7.cod_part IS NULL) OR (b_7.nome IS NULL)) THEN ''::text ELSE ''::text END END), (CASE WHEN (b_7.cnpj IS NULL) THEN ''::bpchar ELSE CASE WHEN (b_7.cnpj = ''::bpchar) THEN b_7.cpf ELSE b_7.cnpj END END), (CASE WHEN (b_7.ie IS NULL) THEN ''::bpchar ELSE b_7.ie END), (((a_7.cod_mod)::text || CASE WHEN (b_3.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_3.descricao)::text) END)), ((a_7.cod_sit)::text || CASE WHEN (b_2.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_2.descricao)::text) END), a_7.ser, a_7.chv_nfe, (CASE WHEN ((a_7.dt_doc = ''::bpchar) OR (a_7.dt_doc IS NULL)) THEN ''::text ELSE ((((""substring""((a_7.dt_doc)::text, 1, 2) || '/'::text) || ""substring""((a_7.dt_doc)::text, 3, 2)) || '/'::text) || ""substring""((a_7.dt_doc)::text, 5, 4)) END), (((((""substring""((a_7.dt_e_s)::text, 1, 2) || '/'::text) || ""substring""((a_7.dt_e_s)::text, 3, 2)) || '/'::text) || ""substring""((a_7.dt_e_s)::text, 5, 4))), (((a_7.vl_doc)::text)::numeric), (((a_7.ind_pgto)::text || CASE WHEN (b_1.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_1.descricao)::text) END)), (((a_7.vl_desc)::text)::numeric), (((a_7.vl_abat_nt)::text)::numeric), (((a_7.vl_merc)::text)::numeric), ((a_7.ind_frt)::text || CASE WHEN (b.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b.descricao)::text) END), (((a_7.vl_frt)::text)::numeric), (((a_7.vl_seg)::text)::numeric), (((a_7.vl_out_da)::text)::numeric), (((a_7.vl_bc_icms)::text)::numeric), (((a_7.vl_icms)::text)::numeric), (((a_7.vl_bc_icms_st)::text)::numeric), (((a_7.vl_icms_st)::text)::numeric), (((a_7.vl_ipi)::text)::numeric), (((a_7.vl_pis)::text)::numeric), (((a_7.vl_confins)::text)::numeric), (((a_7.vl_pis_st)::text)::numeric), (((a_7.vl_confins_st)::text)::numeric), a_7.situacao_conferencia, ((""substring""((b_6.dt_in)::text, 5, 4) || ""substring""((b_6.dt_in)::text, 3, 2))), a_7.fk_0000, (CASE WHEN ((a_7.xml IS NULL) OR (a_7.xml = ''::text)) THEN false ELSE true END)
4. 53.570 1,507.383 ↓ 1.2 36,180 1

Hash Left Join (cost=736.39..66,634.84 rows=30,322 width=1,029) (actual time=271.313..1,507.383 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_sit = b_2.cod_sit)
5. 33.656 1,453.795 ↓ 1.9 36,180 1

Hash Left Join (cost=719.19..65,205.78 rows=18,951 width=1,174) (actual time=271.288..1,453.795 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_pgto = b_1.cod_tipo)
6. 37.827 1,420.126 ↓ 1.9 36,180 1

Hash Left Join (cost=718.10..64,944.11 rows=18,951 width=1,144) (actual time=271.269..1,420.126 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
7. 30.907 1,382.269 ↓ 1.9 36,180 1

Hash Left Join (cost=715.27..64,680.70 rows=18,951 width=1,115) (actual time=271.233..1,382.269 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
8. 30.147 1,351.349 ↓ 1.9 36,180 1

Hash Left Join (cost=714.18..64,419.04 rows=18,951 width=1,085) (actual time=271.213..1,351.349 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
9. 20.922 1,321.189 ↓ 1.9 36,180 1

Hash Left Join (cost=713.13..64,157.42 rows=18,951 width=1,055) (actual time=271.195..1,321.189 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_frt = b.cod_tipo_frt)
10. 473.178 1,300.237 ↓ 1.9 36,180 1

Hash Left Join (cost=712.04..63,706.24 rows=18,951 width=851) (actual time=271.156..1,300.237 rows=36,180 loops=1)

  • Hash Cond: ((a_7.fk_0000 = b_7.fk_0000) AND (a_7.cod_part = b_7.cod_part))
11. 178.003 820.591 ↓ 1.9 36,180 1

Hash Join (cost=6.09..51,048.14 rows=18,951 width=239) (actual time=264.539..820.591 rows=36,180 loops=1)

  • Hash Cond: (a_7.fk_0000 = b_6.id)
12. 642.518 642.518 ↑ 1.0 985,443 1

Seq Scan on reg_c100 a_7 (cost=0.00..48,244.43 rows=985,443 width=230) (actual time=0.049..642.518 rows=985,443 loops=1)

  • Filter: ((ind_oper IS NOT NULL) AND (ind_emit IS NOT NULL) AND (cod_mod IS NOT NULL) AND (cod_sit IS NOT NULL) AND (ind_pgto IS NOT NULL) AND (ind_frt IS NOT NULL))
13. 0.003 0.070 ↑ 1.0 1 1

Hash (cost=6.08..6.08 rows=1 width=13) (actual time=0.070..0.070 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.067 0.067 ↑ 1.0 1 1

Seq Scan on reg_0000 b_6 (cost=0.00..6.08 rows=1 width=13) (actual time=0.067..0.067 rows=1 loops=1)

  • Filter: (((""substring""((dt_in)::text, 5, 4) || ""substring""((dt_in)::text, 3, 2)) >= '201904'::text) AND ((""substring""((dt_in)::text, 5, 4) || ""substring""((dt_in)::text, 3, 2)) <= '201904'::text))
  • Rows Removed by Filter: 51
15. 3.307 6.468 ↑ 1.0 8,278 1

Hash (cost=581.78..581.78 rows=8,278 width=208) (actual time=6.468..6.468 rows=8,278 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2069kB
16. 3.161 3.161 ↑ 1.0 8,278 1

Seq Scan on reg_0150 b_7 (cost=0.00..581.78 rows=8,278 width=208) (actual time=0.016..3.161 rows=8,278 loops=1)

17. 0.005 0.030 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=209) (actual time=0.030..0.030 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.025 0.025 ↑ 1.0 4 1

Seq Scan on indicador_tipo_frete b (cost=0.00..1.04 rows=4 width=209) (actual time=0.024..0.025 rows=4 loops=1)

19. 0.003 0.013 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=109) (actual time=0.013..0.013 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on indicador_tipo_oper b_5 (cost=0.00..1.02 rows=2 width=109) (actual time=0.010..0.010 rows=2 loops=1)

21. 0.003 0.013 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=109) (actual time=0.013..0.013 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on indicador_emitente b_4 (cost=0.00..1.04 rows=4 width=109) (actual time=0.010..0.010 rows=4 loops=1)

23. 0.014 0.030 ↑ 1.0 37 1

Hash (cost=2.37..2.37 rows=37 width=210) (actual time=0.030..0.030 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
24. 0.016 0.016 ↑ 1.0 37 1

Seq Scan on modelo_doc_fiscal b_3 (cost=0.00..2.37 rows=37 width=210) (actual time=0.010..0.016 rows=37 loops=1)

25. 0.003 0.013 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=109) (actual time=0.013..0.013 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on indicador_tipo_pgto b_1 (cost=0.00..1.04 rows=4 width=109) (actual time=0.010..0.010 rows=4 loops=1)

27. 0.006 0.018 ↑ 22.9 14 1

Hash (cost=13.20..13.20 rows=320 width=210) (actual time=0.018..0.018 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.012 0.012 ↑ 22.9 14 1

Seq Scan on sit_doc_fiscal b_2 (cost=0.00..13.20 rows=320 width=210) (actual time=0.009..0.012 rows=14 loops=1)