explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fsij

Settings
# exclusive inclusive rows x rows loops node
1. 532.457 532.457 ↓ 1,578.0 1,578 1

CTE Scan on scores_info (cost=400.05..400.07 rows=1 width=574) (actual time=0.198..532.457 rows=1,578 loops=1)

2.          

CTE scores_info

3. 4.310 529.231 ↓ 1,578.0 1,578 1

Nested Loop (cost=1.95..400.05 rows=1 width=201) (actual time=0.195..529.231 rows=1,578 loops=1)

  • Join Filter: ((((mv.levelcode)::text = 'K'::text) AND ((stu.grade)::text <> 'K'::text) AND ((mv.objectivecode)::text ~~ '%GR1%'::text)) OR (((mv.levelcode)::text = 'K'::text) AND ((stu.grade)::text = 'K'::text) AND ((mv.objectivecode)::text !~~ '%GR1%'::text)) OR ((mv.levelcode)::text <> 'K'::text))
4. 0.918 2.603 ↓ 1,578.0 1,578 1

Nested Loop (cost=1.53..34.51 rows=1 width=96) (actual time=0.062..2.603 rows=1,578 loops=1)

  • Join Filter: (stu.participantid = x.participantid)
5. 0.021 0.185 ↓ 20.0 20 1

Nested Loop (cost=0.84..25.82 rows=1 width=58) (actual time=0.039..0.185 rows=20 loops=1)

6. 0.016 0.016 ↑ 1.0 1 1

Index Scan using udx_dim_participant on dim_participant stu (cost=0.42..8.44 rows=1 width=22) (actual time=0.014..0.016 rows=1 loops=1)

  • Index Cond: ((administrationid = 592030) AND (studentidcode = 104575))
7. 0.148 0.148 ↓ 20.0 20 1

Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt (cost=0.42..17.37 rows=1 width=36) (actual time=0.023..0.148 rows=20 loops=1)

  • Index Cond: ((administrationid = 592030) AND (participantid = stu.participantid))
  • Filter: (((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND ((testingstatus)::text = 'ReportingReady'::text) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
8. 1.500 1.500 ↓ 79.0 79 20

Index Scan using idx_fact_itemresult_1 on fact_itemresult x (cost=0.69..8.67 rows=1 width=78) (actual time=0.021..0.075 rows=79 loops=20)

  • Index Cond: ((administrationid = 592030) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
9. 522.318 522.318 ↑ 1.0 1 1,578

Index Scan using udx_mv_metadata_1 on mv_metadata mv (cost=0.41..365.49 rows=1 width=100) (actual time=0.189..0.331 rows=1 loops=1,578)

  • Index Cond: ((subtestid = x.subtestid) AND (assessmentlevelid = x.assessmentlevelid) AND (assessmentformid = x.assessmentformid) AND (objectiveid = x.objectiveid) AND (assessmentitemid = x.assessmentitemid))
  • Filter: ((((levelcode)::text = 'K'::text) AND ((objectivecode)::text ~~ '%GR1%'::text)) OR (((levelcode)::text = 'K'::text) AND ((objectivecode)::text !~~ '%GR1%'::text)) OR ((levelcode)::text <> 'K'::text))
Planning time : 1.888 ms
Execution time : 532.765 ms