explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WMsD

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 0.423 ↑ 1.0 4 1

GroupAggregate (cost=20,000,000,107.50..20,000,000,107.95 rows=4 width=323) (actual time=0.413..0.423 rows=4 loops=1)

2. 0.043 0.404 ↓ 2.0 8 1

Sort (cost=20,000,000,107.50..20,000,000,107.51 rows=4 width=323) (actual time=0.403..0.404 rows=8 loops=1)

  • Sort Key: (CASE WHEN ((b.per_apur)::text = 'T01'::text) THEN (((b.per_apur)::text || ' - '::text) || '1º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T02'::text) THEN (((b.per_apur)::text || ' - '::text) || '2º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T03'::text) THEN (((b.per_apur)::text || ' - '::text) || '3º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T04'::text) THEN (((b.per_apur)::text || ' - '::text) || '4º Trimestre'::text) ELSE NULL::text END END END END), c.cnpj, b.situacao, (CASE WHEN (""substring""((d.periodo_inicial)::text, 3, 6) IS NULL) THEN ''::text ELSE ((""substring""(""substring""((d.periodo_inicial)::text, 3, 6), 1, 2) || '/'::text) || ""substring""(""substring""((d.periodo_inicial)::text, 3, 6), 3, 4)) END), (CASE WHEN (b.valor_debito IS NULL) THEN 0::numeric ELSE b.valor_debito END), (sum(a.valor)), ((((b.dt_ini)::text || ' até '::text) || (b.dt_fin)::text))
  • Sort Method: quicksort Memory: 26kB
3. 0.002 0.361 ↓ 2.0 8 1

Nested Loop (cost=20,000,000,102.54..20,000,000,107.46 rows=4 width=323) (actual time=0.347..0.361 rows=8 loops=1)

4. 0.009 0.331 ↑ 1.0 4 1

Sort (cost=10,000,000,102.54..10,000,000,102.55 rows=4 width=163) (actual time=0.330..0.331 rows=4 loops=1)

  • Sort Key: (CASE WHEN ((b.per_apur)::text = 'T01'::text) THEN (((b.per_apur)::text || ' - '::text) || '1º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T02'::text) THEN (((b.per_apur)::text || ' - '::text) || '2º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T03'::text) THEN (((b.per_apur)::text || ' - '::text) || '3º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T04'::text) THEN (((b.per_apur)::text || ' - '::text) || '4º Trimestre'::text) ELSE NULL::text END END END END)
  • Sort Method: quicksort Memory: 25kB
5. 0.031 0.322 ↑ 1.0 4 1

Hash Right Join (cost=10,000,000,062.65..10,000,000,102.50 rows=4 width=163) (actual time=0.299..0.322 rows=4 loops=1)

  • Hash Cond: ((b.cnpj_contrib = (c.cnpj)::bpchar) AND (""substring""(""substring""((d.periodo_inicial)::text, 3, 6), 3, 4) = ""substring""(((((b.dt_ini)::text || ' até '::text) || (b.dt_fin)::text)), 1, 4)) AND (b.periodo_dctf = ""substring""((CASE WHEN ((b.per_apur)::text = 'T01'::text) THEN (((b.per_apur)::text || ' - '::text) || '1º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T02'::text) THEN (((b.per_apur)::text || ' - '::text) || '2º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T03'::text) THEN (((b.per_apur)::text || ' - '::text) || '3º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T04'::text) THEN (((b.per_apur)::text || ' - '::text) || '4º Trimestre'::text) ELSE NULL::text END END END END), 7, 12)))
6. 0.004 0.088 ↓ 2.0 2 1

Merge Join (cost=10,000,000,014.70..10,000,000,054.42 rows=1 width=99) (actual time=0.074..0.088 rows=2 loops=1)

  • Merge Cond: (a.id = d.id)
7. 0.014 0.081 ↓ 2.0 2 1

Nested Loop (cost=10,000,000,014.70..10,000,000,035.18 rows=1 width=99) (actual time=0.070..0.081 rows=2 loops=1)

  • Join Filter: ((((substr((a.periodo_inicial)::text, 5, 4) || substr((a.periodo_inicial)::text, 3, 2)))::integer >= (c.data_inicial_auxiliar)::integer) AND (((substr((a.periodo_inicial)::text, 5, 4) || substr((a.periodo_inicial)::text, 3, 2)))::integer <= (c.data_final_auxiliar)::integer))
8. 0.007 0.057 ↑ 1.0 2 1

Merge Join (cost=14.70..33.96 rows=2 width=111) (actual time=0.051..0.057 rows=2 loops=1)

  • Merge Cond: (a.id = b.fk_header)
9. 0.007 0.007 ↑ 165.0 2 1

Index Scan using dctf_header_pkey on dctf_header a (cost=0.00..18.40 rows=330 width=20) (actual time=0.006..0.007 rows=2 loops=1)

10. 0.012 0.043 ↑ 1.0 2 1

Sort (cost=14.70..14.70 rows=2 width=91) (actual time=0.042..0.043 rows=2 loops=1)

  • Sort Key: b.fk_header
  • Sort Method: quicksort Memory: 25kB
11. 0.001 0.031 ↑ 1.0 2 1

Subquery Scan on b (cost=14.53..14.69 rows=2 width=91) (actual time=0.030..0.031 rows=2 loops=1)

12. 0.010 0.030 ↑ 1.0 2 1

HashAggregate (cost=14.53..14.67 rows=2 width=73) (actual time=0.029..0.030 rows=2 loops=1)

13. 0.020 0.020 ↓ 1.5 3 1

Seq Scan on dctf_r10 a (cost=0.00..14.49 rows=2 width=73) (actual time=0.015..0.020 rows=3 loops=1)

  • Filter: (grupo_tributo = '01'::bpchar)
  • Rows Removed by Filter: 2
14. 0.004 0.010 ↑ 1.0 1 2

Materialize (cost=0.00..1.11 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

15. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on data_validacao c (cost=0.00..1.10 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((tipo_arquivo)::text = 'DCTF X SPED ECF'::text)
  • Rows Removed by Filter: 8
16. 0.003 0.003 ↑ 165.0 2 1

Index Scan using dctf_header_pkey on dctf_header d (cost=0.00..18.40 rows=330 width=20) (actual time=0.002..0.003 rows=2 loops=1)

17. 0.103 0.203 ↑ 1.0 4 1

Hash (cost=47.88..47.88 rows=4 width=114) (actual time=0.203..0.203 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
18. 0.007 0.100 ↑ 1.0 4 1

GroupAggregate (cost=47.57..47.84 rows=4 width=34) (actual time=0.097..0.100 rows=4 loops=1)

19. 0.020 0.093 ↑ 1.0 4 1

Sort (cost=47.57..47.58 rows=4 width=34) (actual time=0.093..0.093 rows=4 loops=1)

  • Sort Key: ((((b.dt_ini)::text || ' até '::text) || (b.dt_fin)::text)), (CASE WHEN ((b.per_apur)::text = 'T01'::text) THEN (((b.per_apur)::text || ' - '::text) || '1º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T02'::text) THEN (((b.per_apur)::text || ' - '::text) || '2º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T03'::text) THEN (((b.per_apur)::text || ' - '::text) || '3º Trimestre'::text) ELSE CASE WHEN ((b.per_apur)::text = 'T04'::text) THEN (((b.per_apur)::text || ' - '::text) || '4º Trimestre'::text) ELSE NULL::text END END END END), c.cnpj
  • Sort Method: quicksort Memory: 25kB
20. 0.029 0.073 ↑ 1.0 4 1

Hash Join (cost=15.78..47.53 rows=4 width=34) (actual time=0.063..0.073 rows=4 loops=1)

  • Hash Cond: (b.id = a.fk_p030)
21. 0.007 0.007 ↑ 307.5 4 1

Seq Scan on ecf_p030 b (cost=0.00..22.30 rows=1,230 width=20) (actual time=0.006..0.007 rows=4 loops=1)

22. 0.002 0.037 ↑ 1.0 4 1

Hash (cost=15.73..15.73 rows=4 width=30) (actual time=0.037..0.037 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
23. 0.012 0.035 ↑ 1.0 4 1

Hash Join (cost=2.90..15.73 rows=4 width=30) (actual time=0.034..0.035 rows=4 loops=1)

  • Hash Cond: (c.id = a.fk_0000)
24. 0.004 0.004 ↑ 159.0 1 1

Seq Scan on ecf_0000 c (cost=0.00..11.60 rows=159 width=26) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (cnpj IS NOT NULL)
25. 0.003 0.019 ↑ 1.0 4 1

Hash (cost=2.85..2.85 rows=4 width=20) (actual time=0.019..0.019 rows=4 loops=1)

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

Seq Scan on ecf_p300 a (cost=0.00..2.85 rows=4 width=20) (actual time=0.007..0.016 rows=4 loops=1)

  • Filter: ((codigo)::text = '15'::text)
  • Rows Removed by Filter: 64
27. 0.006 0.028 ↓ 2.0 2 4

Materialize (cost=0.00..4.83 rows=1 width=140) (actual time=0.004..0.007 rows=2 loops=4)

28. 0.022 0.022 ↓ 2.0 2 1

Seq Scan on tabela_aux_receitas_irpj a (cost=0.00..4.82 rows=1 width=140) (actual time=0.012..0.022 rows=2 loops=1)

  • Filter: (((codigo)::text = ANY ('{208901,208908}'::text[])) AND (substr((denominacao)::text, 1, 4) = 'IRPJ'::text))
  • Rows Removed by Filter: 45