explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Wss

Settings
# exclusive inclusive rows x rows loops node
1. 62.211 124,407.005 ↑ 1,469.6 2,458 1

Hash Left Join (cost=3,136,391.98..5,002,463.05 rows=3,612,380 width=1,268) (actual time=61,899.258..124,407.005 rows=2,458 loops=1)

  • Hash Cond: ((a_7.fk_0000 = a.fk_0000) AND (a_3.cod_nat = a.cod_nat))
2. 4.242 124,343.665 ↑ 1,469.6 2,458 1

Hash Left Join (cost=3,136,356.20..4,141,142.95 rows=3,612,380 width=1,633) (actual time=61,897.961..124,343.665 rows=2,458 loops=1)

  • Hash Cond: ((a_7.fk_0000 = a_1.fk_0000) AND (a_3.unid = a_1.unid))
3. 5.858 124,338.618 ↑ 1,469.6 2,458 1

Hash Left Join (cost=3,136,330.90..3,915,393.37 rows=3,612,380 width=1,374) (actual time=61,897.104..124,338.618 rows=2,458 loops=1)

  • Hash Cond: ((a_7.fk_0000 = a_2.fk_0000) AND (a_3.cod_item = a_2.cod_item))
4. 5,137.707 124,304.699 ↑ 1,469.6 2,458 1

Hash Join (cost=3,135,161.45..3,639,462.56 rows=3,612,380 width=1,098) (actual time=61,868.827..124,304.699 rows=2,458 loops=1)

  • Hash Cond: (a_3.fk_c100 = a_7.id)
5. 106,488.139 106,488.139 ↑ 1.0 3,611,083 1

Seq Scan on reg_c170 a_3 (cost=0.00..454,635.83 rows=3,611,083 width=727) (actual time=9.790..106,488.139 rows=3,611,083 loops=1)

  • Filter: ((cod_item IS NOT NULL) AND (unid IS NOT NULL) AND (cod_nat IS NOT NULL))
6. 135.790 12,678.853 ↑ 3.1 60,747 1

Hash (cost=3,132,778.83..3,132,778.83 rows=190,610 width=375) (actual time=12,678.853..12,678.853 rows=60,747 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 25652kB
7. 716.425 12,543.063 ↑ 3.1 60,747 1

Hash Left Join (cost=530.46..3,132,778.83 rows=190,610 width=375) (actual time=8,164.529..12,543.063 rows=60,747 loops=1)

  • Hash Cond: (a_4.uf = a_5.uf_orig)
  • Join Filter: (((CASE WHEN (btrim((a_7.dt_doc)::text) = ''::text) THEN NULL::date ELSE to_date((a_7.dt_doc)::text, 'ddMMyyyy'::text) END) >= to_date((a_5.dt_inicial)::text, 'yyyymmdd'::text)) AND ((CASE WHEN (btrim((a_7.dt_do (...)
  • Rows Removed by Join Filter: 121030
8. 207.724 11,824.428 ↑ 3.1 60,747 1

Hash Left Join (cost=436.57..3,126,401.86 rows=190,610 width=373) (actual time=8,162.305..11,824.428 rows=60,747 loops=1)

  • Hash Cond: ((CASE WHEN (a_6.cod_mun IS NULL) THEN ''::bpchar ELSE a_6.cod_mun END) = a_4.cod_mun)
9. 909.538 11,608.950 ↑ 3.1 60,747 1

Nested Loop Left Join (cost=57.25..3,117,446.10 rows=190,542 width=382) (actual time=8,154.438..11,608.950 rows=60,747 loops=1)

  • Filter: (CASE WHEN (a_6.cod_mun IS NULL) THEN ''::bpchar ELSE a_6.cod_mun END IS NOT NULL)
10.          

Initplan (forNested Loop Left Join)

11. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..1.01 rows=1 width=4) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Seq Scan on tab_cenario_fiscal_parametros c_1 (cost=0.00..1.01 rows=1 width=4) (never executed)

13. 798.879 9,302.231 ↑ 3.2 60,747 1

Hash Join (cost=55.81..2,922,864.60 rows=191,499 width=210) (actual time=8,154.381..9,302.231 rows=60,747 loops=1)

  • Hash Cond: (a_7.fk_0000 = b.fk_0000)
  • Join Filter: ((SubPlan 3) AND (SubPlan 4) AND CASE WHEN (b.tipo = 'C100'::bpchar) THEN ((CASE WHEN (a_7.dt_e_s <> ''::bpchar) THEN to_date((a_7.dt_e_s)::text, 'ddMMyyyy'::text) ELSE to_date((a_7.dt_doc)::te (...)
14. 8,102.425 8,102.425 ↑ 1.0 1,530,592 1

Seq Scan on reg_c100 a_7 (cost=0.00..113,514.79 rows=1,531,994 width=164) (actual time=0.150..8,102.425 rows=1,530,592 loops=1)

  • Filter: ((cod_part IS NOT NULL) AND (id IS NOT NULL))
15. 0.019 36.445 ↑ 70.0 1 1

Hash (cost=54.94..54.94 rows=70 width=102) (actual time=36.445..36.445 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.006 36.426 ↑ 70.0 1 1

Subquery Scan on b (cost=0.00..54.94 rows=70 width=102) (actual time=36.418..36.426 rows=1 loops=1)

17. 36.345 36.420 ↑ 70.0 1 1

Nested Loop (cost=0.00..54.23 rows=70 width=128) (actual time=36.414..36.420 rows=1 loops=1)

  • Join Filter: CASE WHEN (a_8.tipo = '0000'::bpchar) THEN ((to_date("substring"((b_1.dt_in)::text, 3, 6), 'MMyyyy'::text) >= to_date((a_8.data_inicial)::text, 'yyyyMM'::text)) AND (to_date(" (...)
  • Rows Removed by Join Filter: 23
18. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on tab_cenario_fiscal_parametros a_8 (cost=0.00..1.01 rows=1 width=45) (actual time=0.028..0.029 rows=1 loops=1)

19. 0.046 0.046 ↑ 5.8 24 1

Seq Scan on reg_0000 b_1 (cost=0.00..11.40 rows=140 width=27) (actual time=0.021..0.046 rows=24 loops=1)

20.          

SubPlan (forHash Join)

21. 242.988 242.988 ↑ 50.0 2 60,747

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.004..0.004 rows=2 loops=60,747)

22. 121.494 121.494 ↑ 100.0 1 60,747

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.002..0.002 rows=1 loops=60,747)

23. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..1.01 rows=1 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on tab_cenario_fiscal_parametros c (cost=0.00..1.01 rows=1 width=4) (never executed)

25. 1,397.181 1,397.181 ↑ 1.0 1 60,747

Index Scan using reg_0150_cod_part_fk_0000_idx on reg_0150 a_6 (cost=0.42..0.50 rows=1 width=216) (actual time=0.022..0.023 rows=1 loops=60,747)

  • Index Cond: ((cod_part = a_7.cod_part) AND (fk_0000 = a_7.fk_0000))
26. 4.535 7.754 ↑ 1.0 5,570 1

Hash (cost=309.70..309.70 rows=5,570 width=270) (actual time=7.754..7.754 rows=5,570 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1713kB
27. 3.219 3.219 ↑ 1.0 5,570 1

Seq Scan on tab_municipios_ibge a_4 (cost=0.00..309.70 rows=5,570 width=270) (actual time=0.021..3.219 rows=5,570 loops=1)

28. 0.061 2.210 ↓ 5.8 81 1

Hash (cost=93.72..93.72 rows=14 width=26) (actual time=2.210..2.210 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
29. 2.149 2.149 ↓ 5.8 81 1

Seq Scan on tab_aliq_interestaduais a_5 (cost=0.00..93.72 rows=14 width=26) (actual time=0.057..2.149 rows=81 loops=1)

  • Filter: (upper(btrim((uf_orig)::text)) = upper(btrim((uf_dest)::text)))
  • Rows Removed by Filter: 2106
30. 15.685 28.061 ↓ 1.0 8,725 1

Hash (cost=1,038.98..1,038.98 rows=8,698 width=341) (actual time=28.061..28.061 rows=8,725 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3307kB
31. 12.376 12.376 ↓ 1.0 8,725 1

Seq Scan on reg_0200 a_2 (cost=0.00..1,038.98 rows=8,698 width=341) (actual time=0.043..12.376 rows=8,725 loops=1)

32. 0.500 0.805 ↓ 1.0 377 1

Hash (cost=19.72..19.72 rows=372 width=270) (actual time=0.805..0.805 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 120kB
33. 0.305 0.305 ↓ 1.0 377 1

Seq Scan on reg_0190 a_1 (cost=0.00..19.72 rows=372 width=270) (actual time=0.031..0.305 rows=377 loops=1)

34. 0.693 1.129 ↓ 1.0 529 1

Hash (cost=28.11..28.11 rows=511 width=274) (actual time=1.129..1.129 rows=529 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 167kB
35. 0.436 0.436 ↓ 1.0 529 1

Seq Scan on reg_0400 a (cost=0.00..28.11 rows=511 width=274) (actual time=0.021..0.436 rows=529 loops=1)