explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RPnP : Optimization for: Optimization for: plan #N6pm; plan #WZvN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.268 1,818.989 ↓ 2.7 230 1

Sort (cost=139,392.43..139,392.65 rows=86 width=1,005) (actual time=1,818.981..1,818.989 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.129 1,818.721 ↓ 2.7 230 1

Subquery Scan on a (cost=139,380.21..139,389.67 rows=86 width=1,005) (actual time=1,818.122..1,818.721 rows=230 loops=1)

3. 0.460 1,818.592 ↓ 2.7 230 1

Unique (cost=139,380.21..139,388.16 rows=86 width=1,001) (actual time=1,818.114..1,818.592 rows=230 loops=1)

4. 1.595 1,818.132 ↓ 3.9 339 1

Sort (cost=139,380.21..139,380.42 rows=86 width=1,001) (actual time=1,818.113..1,818.132 rows=339 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: 199kB
5. 6.801 1,816.537 ↓ 3.9 339 1

Hash Join (cost=64,862.92..139,377.45 rows=86 width=1,001) (actual time=1,503.129..1,816.537 rows=339 loops=1)

  • Hash Cond: (b.fk_c100 = a_7.id)
6. 709.477 709.477 ↓ 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.113..709.477 rows=12,272 loops=1)

  • Filter: (cfop = '1102'::bpchar)
  • Rows Removed by Filter: 2280991
7. 65.732 1,100.259 ↓ 1.2 36,180 1

Hash (cost=64,483.89..64,483.89 rows=30,322 width=1,855) (actual time=1,100.259..1,100.259 rows=36,180 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 42129kB
8. 17.797 1,034.527 ↓ 1.2 36,180 1

Hash Left Join (cost=736.39..64,483.89 rows=30,322 width=1,855) (actual time=334.469..1,034.527 rows=36,180 loops=1)

  • Hash Cond: (a_7.cod_sit = b_2.cod_sit)
9. 16.606 1,016.713 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_frt = b_1.cod_tipo_frt)
10. 16.217 1,000.093 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_pgto = b_1_1.cod_tipo)
11. 16.484 983.862 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
12. 15.101 967.342 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
13. 15.505 952.216 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
14. 18.170 936.683 ↓ 1.9 36,180 1

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

  • Hash Cond: ((a_7.fk_0000 = b_7.fk_0000) AND (a_7.cod_part = b_7.cod_part))
15. 510.122 911.593 ↓ 1.9 36,180 1

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

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

17. 0.003 0.061 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 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.057..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
19. 3.574 6.920 ↑ 1.0 8,278 1

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

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

21. 0.005 0.028 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.023 0.023 ↑ 1.0 2 1

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

23. 0.015 0.025 ↑ 1.0 4 1

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

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

25. 0.018 0.036 ↑ 1.0 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
26. 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.012..0.018 rows=37 loops=1)

27. 0.004 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
28. 0.010 0.010 ↑ 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.010..0.010 rows=4 loops=1)

29. 0.003 0.014 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=209) (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_frete b_1 (cost=0.00..1.04 rows=4 width=209) (actual time=0.010..0.011 rows=4 loops=1)

31. 0.005 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
32. 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)

Planning time : 31.663 ms