explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZcRQ

Settings
# exclusive inclusive rows x rows loops node
1. 544.341 1,983.206 ↓ 22.5 45 1

GroupAggregate (cost=91.09..92.15 rows=2 width=63) (actual time=1,439.613..1,983.206 rows=45 loops=1)

  • Group Key: a.assignment_id, ca.community_id, cc.pretty_name
  • Filter: (count(CASE (submission_status(s.submission_id) = 'closed'::text) WHEN CASE_TEST_EXPR THEN 1 ELSE NULL::integer END) > 0)
  • Rows Removed by Filter: 232
2. 3.465 1,438.865 ↓ 1,972.0 3,944 1

Sort (cost=91.09..91.10 rows=2 width=59) (actual time=1,438.177..1,438.865 rows=3,944 loops=1)

  • Sort Key: a.assignment_id, ca.community_id, cc.pretty_name
  • Sort Method: quicksort Memory: 649kB
3. 1,424.474 1,435.400 ↓ 1,972.0 3,944 1

Nested Loop (cost=2.54..91.08 rows=2 width=59) (actual time=0.935..1,435.400 rows=3,944 loops=1)

  • Join Filter: ((a.assignment_id = s.assignment_id) AND dotlrn_community__member_p(ca.community_id, s.person_id) AND (NOT dotlrn_community__admin_p(ca.community_id, s.person_id)))
  • Rows Removed by Join Filter: 363
4. 0.304 4.546 ↓ 290.0 290 1

Nested Loop Semi Join (cost=2.11..82.27 rows=1 width=59) (actual time=0.306..4.546 rows=290 loops=1)

5. 0.105 2.502 ↓ 290.0 290 1

Nested Loop (cost=1.69..81.28 rows=1 width=55) (actual time=0.298..2.502 rows=290 loops=1)

6. 0.019 1.309 ↓ 8.0 8 1

Nested Loop (cost=1.27..70.27 rows=1 width=21) (actual time=0.283..1.309 rows=8 loops=1)

  • Join Filter: (ca.community_id = ci.class_instance_id)
7. 0.019 1.226 ↓ 8.0 8 1

Nested Loop (cost=0.85..69.50 rows=1 width=25) (actual time=0.273..1.226 rows=8 loops=1)

8. 1.135 1.135 ↓ 2.7 8 1

Index Scan using dotlrn_community_applets_package_id_idx on dotlrn_community_applets ca (cost=0.43..44.19 rows=3 width=8) (actual time=0.262..1.135 rows=8 loops=1)

  • Index Cond: (package_id = ANY ('{196001803,208230336,209091746,247828512,247842285,247857238,376864268,386459549}'::integer[]))
  • Filter: dotlrn_community__admin_p(community_id, 106150091)
9. 0.072 0.072 ↑ 1.0 1 8

Index Scan using dtlrn_com_all_com_par_id_idx on dotlrn_communities_core cc (cost=0.42..8.44 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=8)

  • Index Cond: (community_id = ca.community_id)
  • Filter: (NOT archived_p)
10. 0.064 0.064 ↑ 1.0 1 8

Index Only Scan using dotlrn_class_instances_pk on dotlrn_class_instances ci (cost=0.42..0.76 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=8)

  • Index Cond: (class_instance_id = cc.community_id)
  • Heap Fetches: 8
11. 1.088 1.088 ↓ 12.0 36 8

Index Scan using assignments_package_id_idx on assignments a (cost=0.42..10.98 rows=3 width=42) (actual time=0.011..0.136 rows=36 loops=8)

  • Index Cond: (package_id = ca.package_id)
  • Filter: (COALESCE(over_due_date, end_date) <= now())
  • Rows Removed by Filter: 8
12. 1.740 1.740 ↑ 1.0 1 290

Index Scan using assignment_tasks_assignment_id_idx on assignment_tasks at (cost=0.42..1.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=290)

  • Index Cond: (assignment_id = a.assignment_id)
  • Filter: ((typ)::text <> 'exam'::text)
13. 6.380 6.380 ↑ 1.0 15 290

Index Scan using submissions_assignment_id_index on submissions s (cost=0.43..1.13 rows=15 width=12) (actual time=0.006..0.022 rows=15 loops=290)

  • Index Cond: (assignment_id = at.assignment_id)
Planning time : 21.665 ms
Execution time : 1,999.953 ms