explain.depesz.com

PostgreSQL's explain analyze made readable

Result: suxS

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

Result (cost=5,097.76..8,773.71 rows=1 width=72) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
2. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=5,097.76..8,773.71 rows=1 width=72) (actual rows= loops=)

  • Join Filter: (q.roster_id = roster.id)
3. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=5,095.56..6,295.76 rows=3 width=72) (actual rows= loops=)

  • Join Filter: (q.question_set_id = question_set.id)
4. 0.000 0.000 ↓ 0.0

Index Scan using quiz_roster_id_index on quiz q (cost=0.28..37.61 rows=624 width=72) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Materialize (cost=5,095.28..6,248.80 rows=1 width=4) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Result (cost=5,095.28..6,248.78 rows=1 width=4) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
7. 0.000 0.000 ↓ 0.0

Seq Scan on question_set (cost=5,095.28..6,248.78 rows=1 width=4) (actual rows= loops=)

  • Filter: (CASE WHEN is_instructor_id((safe_current_setting('jwt.claims.user_id'::text))::integer) THEN (hashed SubPlan 3) ELSE (hashed SubPlan 5) END AND ((permission IS NULL) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'user_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.user_id'::text))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'user_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.user_id'::text))::integer))))) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'org_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.org_id'::text))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'org_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.org_id'::text))::integer))))) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'client_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.client_id'::text))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'client_id'::text) @> to_jsonb((safe_current_setting('jwt.claims.client_id'::text))::integer)))))))
8.          

SubPlan (forSeq Scan)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.20..2,536.44 rows=36 width=4) (actual rows= loops=)

  • Join Filter: (s.id = r.standard_id)
10. 0.000 0.000 ↓ 0.0

Result (cost=1.10..2,474.78 rows=36 width=617) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
11. 0.000 0.000 ↓ 0.0

Seq Scan on roster r (cost=1.10..2,474.78 rows=36 width=617) (actual rows= loops=)

  • Filter: ((org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer) AND (check_instructor_roster(r.*) OR ((SubPlan 2) >= 1) OR (class_teacher_id = (safe_current_setting('jwt.claims.user_id'::text))::integer)))
12.          

SubPlan (forSeq Scan)

13. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.57..8.58 rows=1 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Result (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)) AND ((safe_current_setting('jwt.claims.user_id'::text))::integer = (current_setting('jwt.claims.user_id'::text))::integer))
15. 0.000 0.000 ↓ 0.0

Index Scan using subject_teacher_unique on subject_teacher t_1 (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((roster_id = r.id) AND (teacher_id = (current_setting('jwt.claims.user_id'::text))::integer))
  • Filter: (org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer)
16. 0.000 0.000 ↓ 0.0

Materialize (cost=1.10..55.39 rows=11 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Subquery Scan on s (cost=1.10..55.34 rows=11 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Result (cost=1.10..55.23 rows=11 width=612) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'standard'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'standard'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
19. 0.000 0.000 ↓ 0.0

Seq Scan on standard s_1 (cost=1.10..55.23 rows=11 width=612) (actual rows= loops=)

  • Filter: (org_id = (safe_current_setting('jwt.claims.org_id'::text))::integer)
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.91..2,557.65 rows=1 width=32) (actual rows= loops=)

  • Join Filter: (roster_1.standard_id = standard.id)
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.81..2,502.17 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (subject_teacher.roster_id = roster_1.id)
22. 0.000 0.000 ↓ 0.0

Result (cost=5.71..26.57 rows=1 width=20) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
23. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on subject_teacher (cost=5.71..26.57 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (teacher_id = (safe_current_setting('jwt.claims.user_id'::text))::integer)
  • Filter: (org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer)
24. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on subject_teacher_teacher_id_index (cost=0.00..4.61 rows=11 width=0) (actual rows= loops=)

  • Index Cond: (teacher_id = (safe_current_setting('jwt.claims.user_id'::text))::integer)
25. 0.000 0.000 ↓ 0.0

Result (cost=1.10..2,474.78 rows=36 width=617) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
26. 0.000 0.000 ↓ 0.0

Seq Scan on roster roster_1 (cost=1.10..2,474.78 rows=36 width=617) (actual rows= loops=)

  • Filter: ((org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer) AND (check_instructor_roster(roster_1.*) OR ((SubPlan 4) >= 1) OR (class_teacher_id = (safe_current_setting('jwt.claims.user_id'::text))::integer)))
27.          

SubPlan (forSeq Scan)

28. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.57..8.58 rows=1 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Result (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)) AND ((safe_current_setting('jwt.claims.user_id'::text))::integer = (current_setting('jwt.claims.user_id'::text))::integer))
30. 0.000 0.000 ↓ 0.0

Index Scan using subject_teacher_unique on subject_teacher t_2 (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((roster_id = roster_1.id) AND (teacher_id = (current_setting('jwt.claims.user_id'::text))::integer))
  • Filter: (org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer)
31. 0.000 0.000 ↓ 0.0

Result (cost=1.10..55.23 rows=11 width=612) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'standard'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'standard'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
32. 0.000 0.000 ↓ 0.0

Seq Scan on standard (cost=1.10..55.23 rows=11 width=612) (actual rows= loops=)

  • Filter: (org_id = (safe_current_setting('jwt.claims.org_id'::text))::integer)
33. 0.000 0.000 ↓ 0.0

Materialize (cost=1.10..2,475.32 rows=36 width=4) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Result (cost=1.10..2,474.78 rows=36 width=4) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'roster'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)))
35. 0.000 0.000 ↓ 0.0

Seq Scan on roster (cost=1.10..2,474.78 rows=36 width=4) (actual rows= loops=)

  • Filter: ((org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer) AND (check_instructor_roster(roster.*) OR ((SubPlan 1) >= 1) OR (class_teacher_id = (safe_current_setting('jwt.claims.user_id'::text))::integer)))
36.          

SubPlan (forSeq Scan)

37. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.57..8.58 rows=1 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Result (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • One-Time Filter: (((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'subject_teacher'::text))::text)::integer & 1) > 0)) AND ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((safe_current_setting('jwt.claims.data'::text))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) > 0)) AND ((safe_current_setting('jwt.claims.user_id'::text))::integer = (current_setting('jwt.claims.user_id'::text))::integer))
39. 0.000 0.000 ↓ 0.0

Index Scan using subject_teacher_unique on subject_teacher t (cost=0.28..8.56 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((roster_id = roster.id) AND (teacher_id = (current_setting('jwt.claims.user_id'::text))::integer))
  • Filter: (org_term_id = (safe_current_setting('jwt.claims.org_term_id'::text))::integer)