explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SB3z

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.453 123.922 ↓ 1.1 167 1

Sort (cost=15,779.34..15,779.73 rows=157 width=58) (actual time=123.852..123.922 rows=167 loops=1)

  • Sort Key: b.name
  • Sort Method: quicksort Memory: 37kB
2. 1.995 123.469 ↓ 1.1 167 1

Nested Loop (cost=0.84..15,773.61 rows=157 width=58) (actual time=4.200..123.469 rows=167 loops=1)

  • Join Filter: ((SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5) OR (SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9))
3. 0.006 0.045 ↑ 1.0 1 1

Nested Loop (cost=0.84..8.88 rows=1 width=14) (actual time=0.040..0.045 rows=1 loops=1)

4. 0.029 0.029 ↑ 1.0 1 1

Index Scan using activitytime_pkey on activitytime t (cost=0.42..4.44 rows=1 width=19) (actual time=0.026..0.029 rows=1 loops=1)

  • Index Cond: ((id)::text = '19V-ENG1100-1-1-1-502'::text)
5. 0.010 0.010 ↑ 1.0 1 1

Index Scan using activity_pkey on activity a (cost=0.42..4.44 rows=1 width=36) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: ((id)::text = (t.activityid)::text)
6. 0.103 0.103 ↑ 1.0 167 1

Seq Scan on building b (cost=0.00..4.67 rows=167 width=34) (actual time=0.007..0.103 rows=167 loops=1)

7.          

SubPlan (forNested Loop)

8. 0.501 1.002 ↓ 0.0 0 167

GroupAggregate (cost=0.00..1.08 rows=1 width=70) (actual time=0.006..0.006 rows=0 loops=167)

  • Group Key: b.id
9. 0.501 0.501 ↓ 0.0 0 167

Seq Scan on building_subarea st (cost=0.00..1.06 rows=1 width=43) (actual time=0.003..0.003 rows=0 loops=167)

  • Filter: ((buildingid)::text = (b.id)::text)
  • Rows Removed by Filter: 5
10. 0.668 33.901 ↓ 0.0 0 167

Result (cost=1.29..80.23 rows=5 width=0) (actual time=0.203..0.203 rows=0 loops=167)

  • One-Time Filter: $2
11.          

Initplan (forResult)

12. 0.835 0.835 ↑ 1.0 1 167

Seq Scan on semester z (cost=0.00..1.29 rows=1 width=1) (actual time=0.004..0.005 rows=1 loops=167)

  • Filter: ((id)::text = (a.semesterid)::text)
  • Rows Removed by Filter: 22
13. 32.398 32.398 ↓ 0.0 0 167

Seq Scan on room r (cost=1.29..80.23 rows=5 width=0) (actual time=0.194..0.194 rows=0 loops=167)

  • Filter: (booking AND ((buildingid)::text = (b.id)::text) AND ((active_to IS NULL) OR (active_to >= (now())::date)))
  • Rows Removed by Filter: 898
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on room r_1 (cost=0.00..71.95 rows=1 width=0) (never executed)

  • Filter: (((ownerid)::text = (a.ownerid)::text) AND ((buildingid)::text = (b.id)::text))
15. 0.771 0.771 ↑ 1.0 1,397 1

Seq Scan on room r_2 (cost=0.00..64.97 rows=1,397 width=64) (actual time=0.005..0.771 rows=1,397 loops=1)

16. 0.276 85.652 ↑ 311,531.0 1 92

Nested Loop (cost=0.00..6,529.49 rows=311,531 width=0) (actual time=0.931..0.931 rows=1 loops=92)

17. 0.092 0.092 ↑ 1,397.0 1 92

Seq Scan on room r_3 (cost=0.00..64.97 rows=1,397 width=0) (actual time=0.001..0.001 rows=1 loops=92)

18. 0.368 85.284 ↑ 223.0 1 92

Materialize (cost=0.00..2,570.94 rows=223 width=0) (actual time=0.927..0.927 rows=1 loops=92)

19. 0.552 84.916 ↑ 223.0 1 92

Seq Scan on roomshare rs (cost=0.00..2,569.82 rows=223 width=0) (actual time=0.923..0.923 rows=1 loops=92)

  • Filter: (((departmentid)::text = (a.ownerid)::text) OR (heritage AND (SubPlan 6)))
20.          

SubPlan (forSeq Scan)

21. 84.364 84.364 ↑ 1,000.0 1 92

Function Scan on department_in_department (cost=0.25..10.25 rows=1,000 width=0) (actual time=0.917..0.917 rows=1 loops=92)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using department_pkey on department d (cost=0.27..4.29 rows=1 width=0) (never executed)

  • Index Cond: ((id)::text = (a.ownerid)::text)
  • Filter: ((campusshare)::text = (b.campusshare)::text)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on department d_1 (cost=0.00..5.64 rows=264 width=64) (never executed)