explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5cdF

Settings
# exclusive inclusive rows x rows loops node
1. 671.205 1,871.289 ↓ 5,960.6 3,463,119 1

Nested Loop (cost=1,547.54..2,064.56 rows=581 width=77) (actual time=9.061..1,871.289 rows=3,463,119 loops=1)

2. 1.911 26.164 ↓ 3,680.0 3,680 1

Nested Loop (cost=1,547.11..2,009.82 rows=1 width=77) (actual time=9.036..26.164 rows=3,680 loops=1)

3. 1.654 5.884 ↓ 39.0 39 1

Seq Scan on question_set s (cost=771.36..1,198.13 rows=1 width=4) (actual time=4.742..5.884 rows=39 loops=1)

  • Filter: ((hashed SubPlan 2) AND ((permission IS NULL) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'user_id'::text) @> to_jsonb((current_setting('jwt.claims.user_id'::text, true))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'user_id'::text) @> to_jsonb((current_setting('jwt.claims.user_id'::text, true))::integer))))) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'org_id'::text) @> to_jsonb((current_setting('jwt.claims.org_id'::text, true))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'org_id'::text) @> to_jsonb((current_setting('jwt.claims.org_id'::text, true))::integer))))) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'client_id'::text) @> to_jsonb((current_setting('jwt.claims.client_id'::text, true))::integer)) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'client_id'::text) @> to_jsonb((current_setting('jwt.claims.client_id'::text, true))::integer))))) OR (((permission -> 'include'::text) IS NOT NULL) AND (((permission -> 'include'::text) -> 'role'::text) @> to_jsonb(current_setting('role'::text))) AND (((permission -> 'exclude'::text) IS NULL) OR (NOT (((permission -> 'exclude'::text) -> 'role'::text) @> to_jsonb(current_setting('role'::text))))))) AND ((((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1))) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'question_set'::text))::text)::integer & 15) = 15)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 15) = 15)))))
  • Rows Removed by Filter: 1014
4.          

SubPlan (forSeq Scan)

5. 4.230 4.230 ↓ 711.0 711 1

Seq Scan on "user" user_1 (cost=0.00..771.36 rows=1 width=4) (actual time=0.766..4.230 rows=711 loops=1)

  • Filter: ((((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1)) AND (org_id = (current_setting('jwt.claims.org_id'::text, true))::integer)) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1)) AND (client_id = (current_setting('jwt.claims.client_id'::text, true))::integer)) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 15) = 15)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 15) = 15)) AND (client_id = (current_setting('jwt.claims.client_id'::text, true))::integer)))
  • Rows Removed by Filter: 1217
6. 13.808 18.369 ↓ 94.0 94 39

Bitmap Heap Scan on quiz q (cost=775.74..811.67 rows=1 width=77) (actual time=0.131..0.471 rows=94 loops=39)

  • Recheck Cond: (question_set_id = s.id)
  • Filter: ((hashed SubPlan 1) AND ((((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1))) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'quiz'::text))::text)::integer & 15) = 15)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 15) = 15)))))
  • Heap Blocks: exact=863
7. 0.507 0.507 ↓ 7.2 94 39

Bitmap Index Scan on quiz_question_set_id_index (cost=0.00..4.38 rows=13 width=0) (actual time=0.013..0.013 rows=94 loops=39)

  • Index Cond: (question_set_id = s.id)
8.          

SubPlan (forBitmap Heap Scan)

9. 4.054 4.054 ↓ 711.0 711 1

Seq Scan on "user" (cost=0.00..771.36 rows=1 width=4) (actual time=0.662..4.054 rows=711 loops=1)

  • Filter: ((((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1)) AND (org_id = (current_setting('jwt.claims.org_id'::text, true))::integer)) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 1) = 1)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 1) = 1)) AND (client_id = (current_setting('jwt.claims.client_id'::text, true))::integer)) OR (((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'user'::text))::text)::integer & 15) = 15)) AND ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer IS NULL) OR ((((((((current_setting('jwt.claims.data'::text, true))::json -> 'permission'::text) -> 'access'::text) -> 'all'::text))::text)::integer & 15) = 15)) AND (client_id = (current_setting('jwt.claims.client_id'::text, true))::integer)))
  • Rows Removed by Filter: 1217
10. 1,173.920 1,173.920 ↑ 1.1 941 3,680

Index Only Scan using student_response_quiz_id_index on student_response sr (cost=0.43..44.81 rows=993 width=4) (actual time=0.016..0.319 rows=941 loops=3,680)

  • Index Cond: (quiz_id = q.id)
  • Heap Fetches: 3463119
Planning time : 1.687 ms
Execution time : 2,147.561 ms