explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 42.394 1,611.505 ↓ 1.2 36,180 1

Sort (cost=77,052.54..77,128.34 rows=30,322 width=1,033) (actual time=1,610.183..1,611.505 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.744 1,569.111 ↓ 1.2 36,180 1

Subquery Scan on c100_0150 (cost=71,459.94..74,795.36 rows=30,322 width=1,033) (actual time=1,512.695..1,569.111 rows=36,180 loops=1)

3. 35.041 1,549.367 ↓ 1.2 36,180 1

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

4. 238.067 1,514.326 ↓ 1.2 36,180 1

Sort (cost=71,459.94..71,535.74 rows=30,322 width=1,029) (actual time=1,512.681..1,514.326 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. 254.875 1,276.259 ↓ 1.2 36,180 1

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

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

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

  • Hash Cond: (a_7.ind_frt = b.cod_tipo_frt)
7. 18.429 1,005.182 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_pgto = b_1.cod_tipo)
8. 16.407 986.738 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.cod_mod = b_3.modelo)
9. 15.599 970.299 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_emit = b_4.cod_ind)
10. 16.237 954.685 ↓ 1.9 36,180 1

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

  • Hash Cond: (a_7.ind_oper = b_5.cod_ind)
11. 19.939 938.418 ↓ 1.9 36,180 1

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

  • Hash Cond: ((a_7.fk_0000 = b_7.fk_0000) AND (a_7.cod_part = b_7.cod_part))
12. 520.575 911.949 ↓ 1.9 36,180 1

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

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

14. 0.002 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.060 0.060 ↑ 1.0 1 1

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

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

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

18. 0.005 0.030 ↑ 1.0 2 1

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

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

20. 0.005 0.015 ↑ 1.0 4 1

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

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

22. 0.015 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
23. 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)

24. 0.004 0.015 ↑ 1.0 4 1

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

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

26. 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
27. 0.011 0.011 ↑ 1.0 4 1

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

28. 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
29. 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 : 6.745 ms