explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 44.570 1,946.308 ↓ 1.2 36,180 1

Sort (cost=75,157.41..75,233.22 rows=30,322 width=1,033) (actual time=1,945.035..1,946.308 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. 19.984 1,901.738 ↓ 1.2 36,180 1

Subquery Scan on c100_0150 (cost=69,564.81..72,900.23 rows=30,322 width=1,033) (actual time=1,845.396..1,901.738 rows=36,180 loops=1)

3. 34.812 1,881.754 ↓ 1.2 36,180 1

Unique (cost=69,564.81..72,369.60 rows=30,322 width=1,029) (actual time=1,845.389..1,881.754 rows=36,180 loops=1)

4. 254.830 1,846.942 ↓ 1.2 36,180 1

Sort (cost=69,564.81..69,640.62 rows=30,322 width=1,029) (actual time=1,845.387..1,846.942 rows=36,180 loops=1)

  • Sort Key: ((a_7.id)::text), (formata_intervalo_data((""substring""((r_0000.dt_in)::text, 5, 4) || ""substring""((r_0000.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""((r_0000.dt_in)::text, 5, 4) || ""substring""((r_0000.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)
  • Sort Method: quicksort Memory: 20192kB
5. 214.627 1,592.112 ↓ 1.2 36,180 1

Hash Left Join (cost=736.39..67,307.63 rows=30,322 width=1,029) (actual time=484.793..1,592.112 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_sit = b_2.cod_sit)
6. 35.006 1,377.468 ↓ 1.9 36,180 1

Hash Left Join (cost=719.19..63,452.81 rows=18,951 width=1,174) (actual time=484.710..1,377.468 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_pgto = b_1.cod_tipo)
7. 35.978 1,342.448 ↓ 1.9 36,180 1

Hash Left Join (cost=718.10..63,191.15 rows=18,951 width=1,144) (actual time=484.692..1,342.448 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
8. 31.020 1,306.440 ↓ 1.9 36,180 1

Hash Left Join (cost=715.27..62,927.74 rows=18,951 width=1,115) (actual time=484.656..1,306.440 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
9. 32.577 1,275.407 ↓ 1.9 36,180 1

Hash Left Join (cost=714.18..62,666.07 rows=18,951 width=1,085) (actual time=484.639..1,275.407 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
10. 25.091 1,242.808 ↓ 1.9 36,180 1

Hash Left Join (cost=713.13..62,404.45 rows=18,951 width=1,055) (actual time=484.602..1,242.808 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. 21.067 1,211.129 ↓ 1.9 36,180 1

Hash Left Join (cost=7.18..59,363.98 rows=18,951 width=1,020) (actual time=477.998..1,211.129 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_frt = b.cod_tipo_frt)
12. 483.899 1,190.036 ↓ 1.9 36,180 1

Hash Join (cost=6.09..58,912.81 rows=18,951 width=816) (actual time=477.961..1,190.036 rows=36,180 loops=1)

  • Hash Cond: (a_7.fk_0000 = r_0000.id)
13. 706.075 706.075 ↑ 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.025..706.075 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))
14. 0.009 0.062 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.053 0.053 ↑ 1.0 1 1

Seq Scan on reg_0000 r_0000 (cost=0.00..6.08 rows=1 width=13) (actual time=0.052..0.053 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
16. 0.006 0.026 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.020 0.020 ↑ 1.0 4 1

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

18. 3.588 6.588 ↑ 1.0 8,278 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2069kB
19. 3.000 3.000 ↑ 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.011..3.000 rows=8,278 loops=1)

20. 0.005 0.022 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.017 0.017 ↑ 1.0 2 1

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

22. 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
23. 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.009..0.010 rows=4 loops=1)

24. 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
25. 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)

26. 0.003 0.014 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.011 0.011 ↑ 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.011 rows=4 loops=1)

28. 0.006 0.017 ↑ 22.9 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
29. 0.011 0.011 ↑ 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.011 rows=14 loops=1)

Planning time : 5.688 ms