explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dl39

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 741.344 ↑ 1.0 1 1

Aggregate (cost=1,258.28..1,258.29 rows=1 width=8) (actual time=741.344..741.344 rows=1 loops=1)

2. 0.046 741.313 ↓ 115.0 115 1

Subquery Scan on JJJ (cost=1,258.23..1,258.27 rows=1 width=48) (actual time=741.169..741.313 rows=115 loops=1)

3. 0.081 741.267 ↓ 115.0 115 1

GroupAggregate (cost=1,258.23..1,258.26 rows=1 width=80) (actual time=741.168..741.267 rows=115 loops=1)

  • Group Key: (to_char(SCVIEW.due_date, 'yyyy-MM-DD HH24:MI:SS.MS'::text)), SCVIEW.event_refid
4. 0.146 741.186 ↓ 115.0 115 1

Sort (cost=1,258.23..1,258.24 rows=1 width=48) (actual time=741.161..741.186 rows=115 loops=1)

  • Sort Key: (to_char(SCVIEW.due_date, 'yyyy-MM-DD HH24:MI:SS.MS'::text)) DESC, SCVIEW.event_refid
  • Sort Method: quicksort Memory: 33kB
5. 0.171 741.040 ↓ 115.0 115 1

Subquery Scan on SCVIEW (cost=1,258.21..1,258.22 rows=1 width=48) (actual time=740.853..741.040 rows=115 loops=1)

6. 0.228 740.869 ↓ 115.0 115 1

Sort (cost=1,258.21..1,258.21 rows=1 width=1,275) (actual time=740.840..740.869 rows=115 loops=1)

  • Sort Key: e.due_date DESC, e.activity_id, s.session_id, ((split_part((aid.item_position)::text, '-'::text, 1))::integer)
  • Sort Method: quicksort Memory: 41kB
7. 0.401 740.641 ↓ 115.0 115 1

Nested Loop (cost=8.05..1,258.20 rows=1 width=1,275) (actual time=14.180..740.641 rows=115 loops=1)

8. 182.247 739.550 ↓ 115.0 115 1

Nested Loop (cost=7.63..1,249.74 rows=1 width=74) (actual time=14.159..739.550 rows=115 loops=1)

  • Join Filter: ((e.activity_id = sim.activity_id) AND ((aid.item_ref)::text = (sim.item_ref)::text))
  • Rows Removed by Join Filter: 827,395
9. 1.154 11.513 ↓ 805.0 805 1

Nested Loop (cost=7.07..1,247.71 rows=1 width=126) (actual time=1.379..11.513 rows=805 loops=1)

10. 1.557 4.724 ↓ 805.0 805 1

Nested Loop (cost=6.51..1,239.13 rows=1 width=116) (actual time=1.363..4.724 rows=805 loops=1)

  • Join Filter: (e.activity_id = aid.activity_id)
  • -> Index Scan using assignment_item_activity_idx on assignment_item_details aid (cost=0.43..4.58 rows=16 width=28) (actual time=0.005..0.008 rows=7 loops=115) Index Cond: (activity_id = a.activity_id)
11. 0.132 3.167 ↓ 115.0 115 1

Nested Loop (cost=6.09..1,234.35 rows=1 width=88) (actual time=1.355..3.167 rows=115 loops=1)

  • Join Filter: (e.activity_id = a.activity_id)
12. 0.226 2.460 ↓ 115.0 115 1

Nested Loop (cost=5.67..1,226.11 rows=1 width=72) (actual time=1.344..2.460 rows=115 loops=1)

13. 0.612 0.674 ↓ 3.2 312 1

Bitmap Heap Scan on sessions s (cost=5.24..406.40 rows=96 width=32) (actual time=0.100..0.674 rows=312 loops=1)

  • Recheck Cond: ((student_person_refid)::text = 'e9c7bd53-c729-4ce3-86a4-c34004ecbd66'::text)
  • Filter: ((status)::text <> 'DELETED'::text)
  • Rows Removed by Filter: 57
  • Heap Blocks: exact=309
14. 0.062 0.062 ↓ 3.6 377 1

Bitmap Index Scan on STNX (cost=0.00..5.21 rows=105 width=0) (actual time=0.062..0.062 rows=377 loops=1)

  • Index Cond: ((STid)::text = 'e9c7bd53-c729-4ce3-86a4-c34004ecbd66'::text)
15. 1.560 1.560 ↓ 0.0 0 312

Index Scan using events_pkey on events e (cost=0.43..8.46 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=312)

  • Index Cond: (activity_id = s.activity_refid)
  • Filter: ((due_date >= '2020-07-15 00:00:00'::timestamp without time zone) AND (due_date <= '2021-07-15 00:00:00'::timestamp without time zone) AND (section_id = '52795dd4-ef73-4620-81bc-4addf898192e'::uuid) AND ((discipline_id)::text = 'ED18_SH'::text))
  • Rows Removed by Filter: 1
16. 0.575 0.575 ↑ 1.0 1 115

Index Scan using activities_pkey1 on activities a (cost=0.42..8.23 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=115)

  • Index Cond: (activity_id = s.activity_refid)
  • Filter: ((status)::text <> 'DELETED'::text)
17. 5.635 5.635 ↑ 1.0 1 805

Index Only Scan using assignment_item_scores_item_refid_session_id_key on assignment_item_scores ais (cost=0.56..8.58 rows=1 width=26) (actual time=0.007..0.007 rows=1 loops=805)

  • Index Cond: ((item_refid = (aid.item_ref)::text) AND (session_id = s.session_id))
  • Heap Fetches: 805
18. 545.790 545.790 ↓ 1,028.0 1,028 805

Index Only Scan using sique_key on standard_item_map sim (cost=0.56..2.02 rows=1 width=42) (actual time=0.006..0.678 rows=1,028 loops=805)

  • Index Cond: ((standard_id = 'd28f62e2-a2ab-45ed-99c0-9969fdefa4e8'::uuid) AND (item_ref = (ais.item_refid)::text))
  • Heap Fetches: 827,510
19. 0.690 0.690 ↑ 1.0 1 115

Index Scan using std_standard_pkey on std_standard ss (cost=0.41..8.43 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=115)

  • Index Cond: (standard_id = 'd28f62e2-a2ab-45ed-99c0-9969fdefa4e8'::uuid)
  • Filter: ((standardset_id)::text = 'OneCMS_NRC_D0040816-F39F-11E6-B51B-8662BF03DF2F_SCI_2018'::text)