explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VtyJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=6,159,735.03..6,159,982.43 rows=98,959 width=164) (actual rows= loops=)

  • Sort Key: e1.name, ((SubPlan 2)), e1.extver
2.          

CTE extvsengine

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=884.66..895.83 rows=1 width=33) (actual rows= loops=)

  • Join Filter: (ext.engvernum = e.engvernum)
4. 0.000 0.000 ↓ 0.0

Sort (cost=790.01..790.01 rows=1 width=152) (actual rows= loops=)

  • Sort Key: ev.name, e.engvernum, ev.version
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=584.16..790.00 rows=1 width=152) (actual rows= loops=)

  • Hash Cond: ((e.engvernum = e_1.engvernum) AND (ev.name = ev_1.name) AND (ev.version = ev_1.version) AND (ev.run_id = (max(ev_1.run_id))))
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=87.69..239.57 rows=5,138 width=37) (actual rows= loops=)

  • Hash Cond: (ev.ext_id = e.id)
7. 0.000 0.000 ↓ 0.0

Seq Scan on extver ev (cost=0.00..138.38 rows=5,138 width=31) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=52.86..52.86 rows=2,786 width=22) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on ext e (cost=0.00..52.86 rows=2,786 width=22) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash (cost=393.71..393.71 rows=5,138 width=31) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

HashAggregate (cost=290.95..342.33 rows=5,138 width=31) (actual rows= loops=)

  • Group Key: e_1.engvernum, ev_1.name, ev_1.version
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=87.69..239.57 rows=5,138 width=31) (actual rows= loops=)

  • Hash Cond: (ev_1.ext_id = e_1.id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on extver ev_1 (cost=0.00..138.38 rows=5,138 width=31) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=52.86..52.86 rows=2,786 width=16) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on ext e_1 (cost=0.00..52.86 rows=2,786 width=16) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=94.65..99.53 rows=279 width=18) (actual rows= loops=)

  • Group Key: ext.engvernum, ext.engver, (((ext.engvernum / 100))::integer * 100)
17. 0.000 0.000 ↓ 0.0

Seq Scan on ext (cost=0.00..73.75 rows=2,786 width=18) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Merge Join (cost=356.09..6,142,508.30 rows=98,959 width=164) (actual rows= loops=)

  • Merge Cond: ((e1.majver = emsrc.majver) AND (e1.minver = emsrc.minver_latest))
19. 0.000 0.000 ↓ 0.0

Sort (cost=145.82..148.05 rows=892 width=116) (actual rows= loops=)

  • Sort Key: e1.majver, e1.minver
20. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=0.04..102.10 rows=892 width=116) (actual rows= loops=)

  • Hash Cond: ((emtgt.majver = e3.majver) AND (emtgt.minver_latest = e3.minver) AND (e1.extver = e3.extver))
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..85.71 rows=929 width=120) (actual rows= loops=)

  • Join Filter: (e1.majver < emtgt.majver)
22. 0.000 0.000 ↓ 0.0

CTE Scan on extvsengine e1 (cost=0.00..0.02 rows=1 width=108) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on name_engine emtgt (cost=0.00..50.86 rows=2,786 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=44) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on extvsengine e3 (cost=0.00..0.02 rows=1 width=44) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=210.27..217.24 rows=2,786 width=12) (actual rows= loops=)

  • Sort Key: emsrc.majver, emsrc.minver_latest
27. 0.000 0.000 ↓ 0.0

Seq Scan on name_engine emsrc (cost=0.00..50.86 rows=2,786 width=12) (actual rows= loops=)

28.          

SubPlan (for Merge Join)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on emin (cost=60.65..62.03 rows=50 width=6) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

HashAggregate (cost=60.65..61.53 rows=50 width=18) (actual rows= loops=)

  • Group Key: ext_1.engvernum, ext_1.engver, (((ext_1.engvernum / 100))::integer * 100)
31. 0.000 0.000 ↓ 0.0

Seq Scan on ext ext_1 (cost=0.00..60.24 rows=55 width=18) (actual rows= loops=)

  • Filter: (emtgt.minver_latest = engvernum)
32. 0.000 0.000 ↓ 0.0

CTE Scan on extvsengine e_2 (cost=0.00..0.03 rows=1 width=32) (actual rows= loops=)

  • Filter: ((minver = emtgt.minver_latest) AND (name = e1.name))