explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lBYX : 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 199,500.848 ↓ 0.0 0 1

Result (cost=934,713.76..934,983.13 rows=107 width=296) (actual time=199,500.848..199,500.848 rows=0 loops=1)

2. 0.023 199,500.847 ↓ 0.0 0 1

Sort (cost=934,713.76..934,714.02 rows=107 width=252) (actual time=199,500.847..199,500.847 rows=0 loops=1)

  • Sort Key: (nvl(rltypesort.inorder, 255)), t_1.stname, m.stkeyattr
  • Sort Method: quicksort Memory: 25kB
3. 0.009 199,500.824 ↓ 0.0 0 1

Hash Join (cost=646,814.91..934,710.15 rows=107 width=252) (actual time=199,500.824..199,500.824 rows=0 loops=1)

  • Hash Cond: (ts.inidtype = t1.inid)
4. 0.013 199,500.148 ↓ 0.0 0 1

Hash Join (cost=646,669.86..934,536.88 rows=107 width=252) (actual time=199,500.148..199,500.148 rows=0 loops=1)

  • Hash Cond: (CASE c.inidlock WHEN 0 THEN nvl(e.inidstate, v.inidstate) ELSE v.inidstate END = s.inid)
5. 0.007 199,500.049 ↓ 0.0 0 1

Hash Join (cost=646,658.85..934,523.30 rows=160 width=244) (actual time=199,500.049..199,500.049 rows=0 loops=1)

  • Hash Cond: (v.inid = t.inid)
6. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=646,656.34..915,206.91 rows=5,149,944 width=244) (never executed)

  • Hash Cond: (ts.inidtype = t_1.inid)
7. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=646,610.40..901,425.35 rows=5,149,944 width=220) (never executed)

  • Hash Cond: (v.inlabel = li.inlabel)
8. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=646,590.05..715,559.20 rows=2,239,106 width=75) (never executed)

  • Hash Cond: (c.inidlock = i.inid)
9. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=646,588.45..708,757.34 rows=2,239,106 width=71) (never executed)

  • Hash Cond: (v.inid = p3.inidversion)
10. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=646,588.41..702,879.65 rows=2,239,106 width=67) (never executed)

  • Hash Cond: (v.inid = inidversion)
11. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=646,588.41..694,482.99 rows=2,239,106 width=67) (never executed)

  • Merge Cond: ((ts.inidtype = m.inidtype) AND (ts.inidstate = v.inidstate))
12. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=0.29..544.32 rows=1,000 width=16) (never executed)

  • Merge Cond: ((ts.inidtype = inidtype) AND (ts.inidstate = inidstate))
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rltypesandstates_inidtype_inidstate_key on rltypesandstates ts (cost=0.28..39.27 rows=1,000 width=8) (never executed)

  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.01..0.03 rows=1 width=16) (never executed)

  • Group Key: inidtype, inidstate
15. 0.000 0.000 ↓ 0.0 0

Sort (cost=0.01..0.02 rows=0 width=16) (never executed)

  • Sort Key: inidtype, inidstate
16. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=16) (never executed)

  • One-Time Filter: false
17. 0.000 0.000 ↓ 0.0 0

Materialize (cost=646,580.44..659,062.62 rows=2,496,436 width=55) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Sort (cost=646,580.44..652,821.53 rows=2,496,436 width=55) (never executed)

  • Sort Key: m.inidtype, v.inidstate
19. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=119,409.01..295,986.16 rows=2,496,436 width=55) (never executed)

  • Hash Cond: (l.inidversion = e.inid)
20. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=119,407.99..287,448.22 rows=2,496,436 width=51) (never executed)

  • Hash Cond: (l.state = c.inid)
  • Filter: (((c.inidlock = 0) AND (c.indone <> 2)) OR ((c.inidlock <> 0) AND (c.indone <> 0)) OR (COALESCE((c.indone)::text, ''::text) = ''::text))
21. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=119,406.92..269,833.91 rows=3,209,703 width=51) (never executed)

  • Hash Cond: (v.inidmain = m.inid)
22. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=11.32..76,611.83 rows=3,209,703 width=30) (never executed)

  • Hash Cond: (v.inid = l.inidversion)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on stversions v (cost=0.00..68,175.03 rows=3,209,703 width=22) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.14..11.14 rows=14 width=8) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on stlocks l (cost=0.00..11.14 rows=14 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=58,534.38..58,534.38 rows=3,147,538 width=29) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on stmain m (cost=0.00..58,534.38 rows=3,147,538 width=29) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.03..1.03 rows=3 width=10) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on stchanges c (cost=0.00..1.03 rows=3 width=10) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.01..1.01 rows=1 width=12) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on edtversions e (cost=0.00..1.01 rows=1 width=12) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.00..0.00 rows=0 width=0) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
34. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Subquery Scan on p3 (cost=0.00..0.02 rows=1 width=8) (never executed)

36. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.00..0.01 rows=1 width=8) (never executed)

  • Group Key: inidversion
37. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=8) (never executed)

  • One-Time Filter: false
38. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.27..1.27 rows=27 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on stlockinfo i (cost=0.00..1.27 rows=27 width=8) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash (cost=14.60..14.60 rows=460 width=149) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on dslabels li (cost=0.00..14.60 rows=460 width=149) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash (cost=42.64..42.64 rows=264 width=24) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on dstypes t_1 (cost=0.00..42.64 rows=264 width=24) (never executed)

44. 0.007 199,500.042 ↓ 0.0 0 1

Hash (cost=1.25..1.25 rows=100 width=4) (actual time=199,500.042..199,500.042 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
45. 199,500.035 199,500.035 ↓ 0.0 0 1

Function Scan on unnest t (cost=0.25..1.25 rows=100 width=4) (actual time=199,500.035..199,500.035 rows=0 loops=1)

46. 0.032 0.086 ↑ 1.0 134 1

Hash (cost=9.34..9.34 rows=134 width=20) (actual time=0.086..0.086 rows=134 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
47. 0.054 0.054 ↑ 1.0 134 1

Seq Scan on dsstates s (cost=0.00..9.34 rows=134 width=20) (actual time=0.007..0.054 rows=134 loops=1)

48. 0.084 0.667 ↑ 1.0 264 1

Hash (cost=141.75..141.75 rows=264 width=8) (actual time=0.667..0.667 rows=264 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
49. 0.145 0.583 ↑ 1.0 264 1

Hash Right Join (cost=21.74..141.75 rows=264 width=8) (actual time=0.198..0.583 rows=264 loops=1)

  • Hash Cond: (rltypesort.inidtype = t1.inid)
50. 0.268 0.305 ↑ 1.0 264 1

Bitmap Heap Scan on rltypesort (cost=6.33..125.63 rows=264 width=8) (actual time=0.058..0.305 rows=264 loops=1)

  • Recheck Cond: (inidcase = 1)
  • Heap Blocks: exact=73
51. 0.037 0.037 ↑ 1.0 264 1

Bitmap Index Scan on idx_rlsorttype_inidcase (cost=0.00..6.27 rows=264 width=0) (actual time=0.036..0.037 rows=264 loops=1)

  • Index Cond: (inidcase = 1)
52. 0.053 0.133 ↑ 1.0 264 1

Hash (cost=12.11..12.11 rows=264 width=4) (actual time=0.133..0.133 rows=264 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
53. 0.080 0.080 ↑ 1.0 264 1

Index Only Scan using dstypes_pkey on dstypes t1 (cost=0.15..12.11 rows=264 width=4) (actual time=0.039..0.080 rows=264 loops=1)

  • Heap Fetches: 0
Planning time : 10.595 ms
Execution time : 199,501.464 ms