explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WZvN : Optimization for: plan #N6pm

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.259 2,139.809 ↓ 2.7 230 1

Sort (cost=149,152.29..149,152.50 rows=86 width=1,005) (actual time=2,139.800..2,139.809 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.170 2,139.550 ↓ 2.7 230 1

Subquery Scan on a (cost=145,947.22..149,149.53 rows=86 width=1,005) (actual time=2,092.546..2,139.550 rows=230 loops=1)

3. 0.601 2,139.380 ↓ 2.7 230 1

Unique (cost=145,947.22..149,148.02 rows=86 width=1,001) (actual time=2,092.537..2,139.380 rows=230 loops=1)

4. 3.083 2,138.779 ↓ 3.9 339 1

Merge Join (cost=145,947.22..149,140.28 rows=86 width=1,001) (actual time=2,092.535..2,138.779 rows=339 loops=1)

  • Merge Cond: (a_7.id = b.fk_c100)
5. 42.203 1,468.438 ↓ 1.2 36,163 1

Unique (cost=71,365.18..74,169.97 rows=30,322 width=1,001) (actual time=1,424.648..1,468.438 rows=36,163 loops=1)

6. 50.902 1,426.235 ↓ 1.2 36,163 1

Sort (cost=71,365.18..71,440.99 rows=30,322 width=1,001) (actual time=1,424.640..1,426.235 rows=36,163 loops=1)

  • Sort Key: a_7.id, (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
7. 279.538 1,375.333 ↓ 1.2 36,180 1

Hash Left Join (cost=736.39..69,108.00 rows=30,322 width=1,001) (actual time=361.572..1,375.333 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_sit = b_2.cod_sit)
8. 19.744 1,095.779 ↓ 1.9 36,180 1

Hash Left Join (cost=719.19..63,358.06 rows=18,951 width=1,651) (actual time=361.532..1,095.779 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_frt = b_1.cod_tipo_frt)
9. 16.594 1,076.022 ↓ 1.9 36,180 1

Hash Left Join (cost=718.10..63,096.39 rows=18,951 width=1,447) (actual time=361.514..1,076.022 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_pgto = b_1_1.cod_tipo)
10. 18.449 1,059.414 ↓ 1.9 36,180 1

Hash Left Join (cost=717.01..62,834.72 rows=18,951 width=1,343) (actual time=361.496..1,059.414 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
11. 17.242 1,040.934 ↓ 1.9 36,180 1

Hash Left Join (cost=714.18..62,571.32 rows=18,951 width=1,139) (actual time=361.459..1,040.934 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
12. 18.016 1,023.678 ↓ 1.9 36,180 1

Hash Left Join (cost=713.09..62,309.65 rows=18,951 width=1,035) (actual time=361.441..1,023.678 rows=36,180 loops=1)

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
13. 19.779 1,005.633 ↓ 1.9 36,180 1

Hash Left Join (cost=712.04..62,048.03 rows=18,951 width=931) (actual time=361.399..1,005.633 rows=36,180 loops=1)

  • Hash Cond: ((a_7.fk_0000 = b_7.fk_0000) AND (a_7.cod_part = b_7.cod_part))
14. 554.964 977.369 ↓ 1.9 36,180 1

Hash Join (cost=6.09..58,818.05 rows=18,951 width=788) (actual time=352.830..977.369 rows=36,180 loops=1)

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

16. 0.003 0.059 ↑ 1.0 1 1

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

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

Seq Scan on reg_0000 r_0000 (cost=0.00..6.08 rows=1 width=13) (actual time=0.056..0.056 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
18. 3.814 8.485 ↑ 1.0 8,278 1

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

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

20. 0.004 0.029 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.025 0.025 ↑ 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.025 rows=2 loops=1)

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

24. 0.014 0.031 ↑ 1.0 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
25. 0.017 0.017 ↑ 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.017 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_1 (cost=0.00..1.04 rows=4 width=109) (actual time=0.011..0.011 rows=4 loops=1)

28. 0.003 0.013 ↑ 1.0 4 1

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

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

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

30. 0.005 0.016 ↑ 22.9 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
31. 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)

32. 3.220 667.258 ↓ 7.3 12,272 1

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

  • Sort Key: b.fk_c100
  • Sort Method: quicksort Memory: 960kB
33. 664.038 664.038 ↓ 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.070..664.038 rows=12,272 loops=1)

  • Filter: (cfop = '1102'::bpchar)
  • Rows Removed by Filter: 2280991