explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N6pm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.277 2,570.709 ↓ 2.7 230 1

Sort (cost=151,743.01..151,743.22 rows=86 width=1,033) (actual time=2,570.700..2,570.709 rows=230 loops=1)

  • Sort Key: (CASE WHEN (a.num_doc = ''::bpchar) THEN 0 ELSE (a.num_doc)::integer END)
  • Sort Method: quicksort Memory: 143kB
2. 0.132 2,570.432 ↓ 2.7 230 1

Subquery Scan on a (cost=151,730.79..151,740.25 rows=86 width=1,033) (actual time=2,568.678..2,570.432 rows=230 loops=1)

3. 1.615 2,570.300 ↓ 2.7 230 1

Unique (cost=151,730.79..151,738.74 rows=86 width=1,029) (actual time=2,568.671..2,570.300 rows=230 loops=1)

4. 1.729 2,568.685 ↓ 3.9 339 1

Sort (cost=151,730.79..151,731.00 rows=86 width=1,029) (actual time=2,568.671..2,568.685 rows=339 loops=1)

  • Sort Key: a_1.id, a_1.mes_referencia, a_1.num_doc, a_1.ind_oper, a_1.ind_emit, a_1.cnpj_cpf, a_1.cnpjcpf_pesq, a_1.ie, a_1.cod_mod, a_1.cod_sit, a_1.ser, a_1.chv_nfe, a_1.dt_doc, a_1.dt_e_s, a_1.vl_doc, a_1.ind_pgto, a_1.vl_desc, a_1.vl_abat_nt, a_1.vl_merc, a_1.ind_frt, a_1.vl_frt, a_1.vl_seg, a_1.vl_out_da, a_1.vl_bc_icms, a_1.vl_icms, a_1.vl_bc_icms_st, a_1.vl_icms_st, a_1.vl_ipi, a_1.vl_pis, a_1.vl_confins, a_1.vl_pis_st, a_1.vl_confins_st, a_1.situacao_conferencia, a_1.dt_in, a_1.fk_0000, a_1.xml
  • Sort Method: quicksort Memory: 199kB
5. 11.718 2,566.956 ↓ 3.9 339 1

Merge Join (cost=151,407.16..151,728.02 rows=86 width=1,029) (actual time=2,556.203..2,566.956 rows=339 loops=1)

  • Merge Cond: (((a_1.id)::integer) = b.fk_c100)
6. 45.027 1,853.508 ↓ 1.2 36,163 1

Sort (cost=76,825.12..76,900.93 rows=30,322 width=1,029) (actual time=1,852.305..1,853.508 rows=36,163 loops=1)

  • Sort Key: ((a_1.id)::integer)
  • Sort Method: quicksort Memory: 20192kB
7. 19.004 1,808.481 ↓ 1.2 36,180 1

Subquery Scan on a_1 (cost=71,459.94..74,567.94 rows=30,322 width=1,029) (actual time=1,747.767..1,808.481 rows=36,180 loops=1)

8. 37.527 1,789.477 ↓ 1.2 36,180 1

Unique (cost=71,459.94..74,264.72 rows=30,322 width=1,029) (actual time=1,747.761..1,789.477 rows=36,180 loops=1)

9. 254.656 1,751.950 ↓ 1.2 36,180 1

Sort (cost=71,459.94..71,535.74 rows=30,322 width=1,029) (actual time=1,747.759..1,751.950 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_1.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_1_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_1.descricao IS NULL) THEN ''::text ELSE (' - '::text || (b_1.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
10. 275.510 1,497.294 ↓ 1.2 36,180 1

Hash Left Join (cost=736.39..69,202.75 rows=30,322 width=1,029) (actual time=505.291..1,497.294 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_sit = b_2.cod_sit)
11. 19.430 1,221.766 ↓ 1.9 36,180 1

Hash Left Join (cost=719.19..63,452.81 rows=18,951 width=1,679) (actual time=505.193..1,221.766 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_frt = b_1.cod_tipo_frt)
12. 15.453 1,202.321 ↓ 1.9 36,180 1

Hash Left Join (cost=718.10..63,191.15 rows=18,951 width=1,475) (actual time=505.173..1,202.321 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_pgto = b_1_1.cod_tipo)
13. 16.096 1,186.854 ↓ 1.9 36,180 1

Hash Left Join (cost=717.01..62,929.48 rows=18,951 width=1,371) (actual time=505.154..1,186.854 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
14. 15.288 1,170.726 ↓ 1.9 36,180 1

Hash Left Join (cost=714.18..62,666.07 rows=18,951 width=1,167) (actual time=505.118..1,170.726 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
15. 16.365 1,155.423 ↓ 1.9 36,180 1

Hash Left Join (cost=713.09..62,404.40 rows=18,951 width=1,063) (actual time=505.098..1,155.423 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
16. 20.565 1,139.027 ↓ 1.9 36,180 1

Hash Left Join (cost=712.04..62,142.78 rows=18,951 width=959) (actual time=505.054..1,139.027 rows=36,180 loops=1)

  • Hash Cond: ((a_7.fk_0000 = b_7.fk_0000) AND (a_7.cod_part = b_7.cod_part))
17. 583.978 1,111.838 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.fk_0000 = r_0000.id)
18. 527.798 527.798 ↑ 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.032..527.798 rows=985,443 loops=1)

19. 0.004 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
20. 0.058 0.058 ↑ 1.0 1 1

Seq Scan on reg_0000 r_0000 (cost=0.00..6.08 rows=1 width=13) (actual time=0.058..0.058 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
21. 3.739 6.624 ↑ 1.0 8,278 1

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

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

23. 0.005 0.031 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.026 0.026 ↑ 1.0 2 1

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

25. 0.004 0.015 ↑ 1.0 4 1

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

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

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

27. 0.014 0.032 ↑ 1.0 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
28. 0.018 0.018 ↑ 1.0 37 1

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

29. 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
30. 0.011 0.011 ↑ 1.0 4 1

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

31. 0.003 0.015 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.012 0.012 ↑ 1.0 4 1

Seq Scan on indicador_tipo_frete b_1 (cost=0.00..1.04 rows=4 width=209) (actual time=0.010..0.012 rows=4 loops=1)

33. 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
34. 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.010..0.012 rows=14 loops=1)

35. 3.363 701.730 ↓ 7.3 12,272 1

Sort (cost=74,582.04..74,586.24 rows=1,678 width=4) (actual time=701.115..701.730 rows=12,272 loops=1)

  • Sort Key: b.fk_c100
  • Sort Method: quicksort Memory: 960kB
36. 698.367 698.367 ↓ 7.3 12,272 1

Seq Scan on reg_c190 b (cost=0.00..74,492.16 rows=1,678 width=4) (actual time=0.076..698.367 rows=12,272 loops=1)

  • Filter: (cfop = '1102'::bpchar)
  • Rows Removed by Filter: 2280991
Planning time : 7.219 ms