explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ESIi

Settings
# exclusive inclusive rows x rows loops node
1. 0.701 12,137.828 ↑ 1.0 3 1

Index Scan using xifesajclasse on esajclasse c (cost=0.28..236,048.79 rows=3 width=73) (actual time=29.231..12,137.828 rows=3 loops=1)

  • Filter: (cdclasseext = ANY ('{279,280,12121}'::numeric[]))
  • Rows Removed by Filter: 736
2.          

SubPlan (for Index Scan)

3. 1.461 12,137.127 ↑ 1.0 1 3

Aggregate (cost=78,630.20..78,630.21 rows=1 width=8) (actual time=4,045.709..4,045.709 rows=1 loops=3)

4. 4.593 12,135.666 ↓ 89.4 1,699 3

Hash Join (cost=6,087.42..78,630.16 rows=19 width=14) (actual time=33.528..4,045.222 rows=1,699 loops=3)

  • Hash Cond: (p.cdlocal = (l.cdlocal)::numeric)
5. 7.228 12,130.536 ↓ 106.2 1,699 3

Hash Join (cost=6,069.37..78,609.20 rows=16 width=20) (actual time=33.344..4,043.512 rows=1,699 loops=3)

  • Hash Cond: ((mv.cdtipomvprocesso)::text = (tpmv.cdtipomvprocesso)::text)
6. 4.884 12,122.049 ↓ 198.3 6,545 3

Nested Loop (cost=6,052.12..78,591.69 rows=33 width=23) (actual time=30.518..4,040.683 rows=6,545 loops=3)

7. 75.317 11,981.061 ↓ 5.3 3,781 3

Bitmap Heap Scan on efmpprocesso p (cost=6,051.56..74,888.04 rows=718 width=20) (actual time=30.496..3,993.687 rows=3,781 loops=3)

  • Recheck Cond: ((cdclasseprocesso = c.cdclasse) AND ((cdtipoprocesso)::text = ANY ('{0801,0802}'::text[])))
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 19957
  • Heap Blocks: exact=26668
8. 1.137 84.552 ↓ 0.0 0 3

BitmapAnd (cost=6,051.56..6,051.56 rows=1,435 width=0) (actual time=28.184..28.184 rows=0 loops=3)

9. 9.081 9.081 ↓ 12.3 23,970 3

Bitmap Index Scan on xif2184efmpprocesso (cost=0.00..39.06 rows=1,952 width=0) (actual time=3.027..3.027 rows=23,970 loops=3)

  • Index Cond: (cdclasseprocesso = c.cdclasse)
10. 74.334 74.334 ↑ 1.0 396,367 3

Bitmap Index Scan on xif2182efmpprocesso (cost=0.00..6,011.89 rows=396,672 width=0) (actual time=24.778..24.778 rows=396,367 loops=3)

  • Index Cond: ((cdtipoprocesso)::text = ANY ('{0801,0802}'::text[]))
11.          

SubPlan (for Bitmap Heap Scan)

12. 142.424 11,821.192 ↓ 0.0 0 71,212

Aggregate (cost=44.83..44.84 rows=1 width=4) (actual time=0.166..0.166 rows=0 loops=71,212)

  • Filter: (COALESCE(max(f.nuseqprocessomv), '-1'::numeric) <= $2)
  • Rows Removed by Filter: 1
13.          

Initplan (for Aggregate)

14. 0.000 4,913.628 ↑ 1.0 1 71,212

Aggregate (cost=22.41..22.42 rows=1 width=32) (actual time=0.069..0.069 rows=1 loops=71,212)

15. 105.301 4,913.628 ↓ 0.0 0 71,212

Nested Loop Semi Join (cost=1.11..22.41 rows=1 width=4) (actual time=0.069..0.069 rows=0 loops=71,212)

16. 925.756 925.756 ↓ 8.0 8 71,212

Index Only Scan using xddmfmpprocessomv on efmpprocessomv r (cost=0.56..5.30 rows=1 width=7) (actual time=0.011..0.013 rows=8 loops=71,212)

  • Index Cond: (cdprocesso = p.cdprocesso)
  • Filter: (((dtmovimento)::date >= '2019-01-01'::date) AND ((dtmovimento)::date <= '2019-12-31'::date))
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
17. 554.653 3,882.571 ↓ 0.0 0 554,653

Nested Loop Semi Join (cost=0.55..17.10 rows=1 width=3) (actual time=0.007..0.007 rows=0 loops=554,653)

  • Join Filter: (efmptipomvprocesso.cdtipomvext = ((((unnest(string_to_array((epadvalorparametro.vlparametro)::text, ','::text))))::integer))::numeric)
  • Rows Removed by Join Filter: 2
18. 1,663.959 1,663.959 ↑ 1.0 1 554,653

Index Scan using idx_efmptipomvproc_detipomv on efmptipomvprocesso (cost=0.28..8.29 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=554,653)

  • Index Cond: ((cdtipomvprocesso)::text = (r.cdtipomvprocesso)::text)
19. 0.000 1,663.959 ↑ 5.0 2 554,653

Result (cost=0.28..8.55 rows=10 width=4) (actual time=0.003..0.003 rows=2 loops=554,653)

20. 554.653 1,663.959 ↑ 5.0 2 554,653

ProjectSet (cost=0.28..8.36 rows=10 width=32) (actual time=0.002..0.003 rows=2 loops=554,653)

21. 1,109.306 1,109.306 ↑ 1.0 1 554,653

Index Scan using xif51epadvalorparm on epadvalorparametro (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=554,653)

  • Index Cond: (cdparametro = '51523'::numeric)
22. 79.218 6,765.140 ↑ 1.0 1 71,212

Nested Loop Semi Join (cost=1.11..22.41 rows=1 width=4) (actual time=0.075..0.095 rows=1 loops=71,212)

23. 1,139.392 1,139.392 ↓ 8.0 8 71,212

Index Only Scan using xddmfmpprocessomv on efmpprocessomv f (cost=0.56..5.30 rows=1 width=7) (actual time=0.014..0.016 rows=8 loops=71,212)

  • Index Cond: (cdprocesso = p.cdprocesso)
  • Filter: (((dtmovimento)::date >= '2019-01-01'::date) AND ((dtmovimento)::date <= '2019-12-31'::date))
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
24. 1,109.306 5,546.530 ↓ 0.0 0 554,653

Nested Loop Semi Join (cost=0.55..17.10 rows=1 width=3) (actual time=0.010..0.010 rows=0 loops=554,653)

  • Join Filter: (efmptipomvprocesso_1.cdtipomvext = ((((unnest(string_to_array((epadvalorparametro_1.vlparametro)::text, ','::text))))::integer))::numeric)
  • Rows Removed by Join Filter: 9
25. 1,663.959 1,663.959 ↑ 1.0 1 554,653

Index Scan using idx_efmptipomvproc_detipomv on efmptipomvprocesso efmptipomvprocesso_1 (cost=0.28..8.29 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=554,653)

  • Index Cond: ((cdtipomvprocesso)::text = (f.cdtipomvprocesso)::text)
26. 554.653 2,773.265 ↑ 1.1 9 554,653

Result (cost=0.28..8.55 rows=10 width=4) (actual time=0.003..0.005 rows=9 loops=554,653)

27. 1,109.306 2,218.612 ↑ 1.1 9 554,653

ProjectSet (cost=0.28..8.36 rows=10 width=32) (actual time=0.003..0.004 rows=9 loops=554,653)

28. 1,109.306 1,109.306 ↑ 1.0 1 554,653

Index Scan using xif51epadvalorparm on epadvalorparametro epadvalorparametro_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=554,653)

  • Index Cond: (cdparametro = '51520'::numeric)
29. 136.104 136.104 ↓ 2.0 2 11,342

Index Only Scan using xddmfmpprocessomv on efmpprocessomv mv (cost=0.56..5.15 rows=1 width=17) (actual time=0.011..0.012 rows=2 loops=11,342)

  • Index Cond: (cdprocesso = p.cdprocesso)
  • Filter: (date_part('year'::text, dtmovimento) = '2019'::double precision)
  • Rows Removed by Filter: 3
  • Heap Fetches: 0
30. 0.003 1.259 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=32) (actual time=1.258..1.259 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.010 1.256 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=1.254..1.256 rows=1 loops=1)

  • Group Key: (tpmv.cdtipomvprocesso)::text
32. 1.246 1.246 ↑ 1,000.0 1 1

Function Scan on fnarvoremovimentacao tpmv (cost=0.25..10.25 rows=1,000 width=32) (actual time=1.246..1.246 rows=1 loops=1)

33. 0.381 0.537 ↑ 1.0 669 1

Hash (cost=9.69..9.69 rows=669 width=4) (actual time=0.537..0.537 rows=669 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
34. 0.156 0.156 ↑ 1.0 669 1

Seq Scan on esajlocaltemp l (cost=0.00..9.69 rows=669 width=4) (actual time=0.015..0.156 rows=669 loops=1)

Planning time : 4.965 ms
Execution time : 12,138.099 ms