explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EPSx

Settings
# exclusive inclusive rows x rows loops node
1. 0.712 12,201.142 ↑ 1.0 3 1

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

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

SubPlan (for Index Scan)

3. 0.006 12,200.430 ↑ 1.0 1 3

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

4. 0.008 12,200.424 ↓ 0.0 0 3

Hash Join (cost=6,087.42..78,630.16 rows=19 width=14) (actual time=4,066.808..4,066.808 rows=0 loops=3)

  • Hash Cond: (p.cdlocal = (l.cdlocal)::numeric)
5. 0.011 12,200.130 ↓ 0.0 0 3

Hash Join (cost=6,069.37..78,609.20 rows=16 width=20) (actual time=4,066.710..4,066.710 rows=0 loops=3)

  • Hash Cond: ((mv.cdtipomvprocesso)::text = (tpmv.cdtipomvprocesso)::text)
6. 0.024 12,199.497 ↓ 0.0 0 3

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

7. 81.695 12,199.473 ↓ 0.0 0 3

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

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

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

9. 7.950 7.950 ↓ 12.3 23,970 3

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

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

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

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

SubPlan (for Bitmap Heap Scan)

12. 142.424 12,034.828 ↓ 0.0 0 71,212

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

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

Initplan (for Aggregate)

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

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

15. 176.513 4,984.840 ↓ 0.0 0 71,212

Nested Loop Semi Join (cost=1.11..22.41 rows=1 width=4) (actual time=0.070..0.070 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. 150.430 6,907.564 ↑ 1.0 1 71,212

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

23. 1,210.604 1,210.604 ↓ 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.017 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. 0.000 0.000 ↓ 0.0 0

Index Only Scan using xddmfmpprocessomv on efmpprocessomv mv (cost=0.56..5.15 rows=1 width=17) (never executed)

  • Index Cond: (cdprocesso = p.cdprocesso)
  • Filter: (date_part('year'::text, dtmovimento) = '2019'::double precision)
  • Heap Fetches: 0
30. 0.002 0.622 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=32) (actual time=0.621..0.622 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.007 0.620 ↑ 200.0 1 1

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

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

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

33. 0.186 0.286 ↑ 1.0 669 1

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

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

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

Planning time : 2.547 ms
Execution time : 12,201.338 ms