explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txYv

Settings
# exclusive inclusive rows x rows loops node
1. 0.628 33.594 ↓ 789.0 1,578 1

Unique (cost=54.63..54.73 rows=2 width=678) (actual time=32.897..33.594 rows=1,578 loops=1)

2.          

CTE scores_info

3. 2.748 7.046 ↓ 1,578.0 1,578 1

Nested Loop (cost=1.95..35.02 rows=1 width=201) (actual time=0.092..7.046 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.445 1.142 ↓ 1,578.0 1,578 1

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

  • Join Filter: (stu.participantid = x_2.participantid)
5. 0.005 0.077 ↓ 20.0 20 1

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

6. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

  • Index Cond: ((administrationid = 592030) AND (studentidcode = 104575))
7. 0.055 0.055 ↓ 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.027..0.055 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. 0.620 0.620 ↓ 79.0 79 20

Index Scan using idx_fact_itemresult_1 on fact_itemresult x_2 (cost=0.69..8.67 rows=1 width=78) (actual time=0.008..0.031 rows=79 loops=20)

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

Index Scan using udx_mv_metadata_1 on mv_metadata mv (cost=0.41..0.46 rows=1 width=104) (actual time=0.002..0.002 rows=1 loops=1,578)

  • Index Cond: ((administrationid = 592030) AND (subtestid = x_2.subtestid) AND (assessmentlevelid = x_2.assessmentlevelid) AND (assessmentformid = x_2.assessmentformid) AND (objectiveid = x_2.objectiveid) AND (assessmentitemid = x_2.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))
10. 9.422 32.966 ↓ 789.0 1,578 1

Sort (cost=19.60..19.61 rows=2 width=678) (actual time=32.896..32.966 rows=1,578 loops=1)

  • Sort Key: ('<p_CLIENTID>'::text), x.administrationid, ((((dst.orgcode)::text || '~'::text) || (sch.orgcode)::text)), x.drcstudentid, x.drcassessmentdocumentid, x.drcsubjectobjectiveid, x.drcitemid, (((((('<p_CLIENTID>_'::text || (x.administrationid)::text) || '_'::text) || (dst.orgcode)::text) || '_'::text) || (sch.orgcode)::text)), x.itemname, x.itemtype, x."position", x.rawscore, x.maxscore, x.response, x.correct, x.attempt, x.drcresponseguid, x.detailslink, (to_char(LOCALTIMESTAMP(0), 'fmMM/fmDD/YYYY fmHH12:fmMI:fmSS AM'::text))
  • Sort Method: quicksort Memory: 468kB
11. 0.129 23.544 ↓ 789.0 1,578 1

Append (cost=0.57..19.59 rows=2 width=678) (actual time=0.127..23.544 rows=1,578 loops=1)

12. 3.093 15.758 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.57..8.93 rows=1 width=678) (actual time=0.127..15.758 rows=1,578 loops=1)

  • Join Filter: (x.administrationid = dst.administrationid)
13. 1.499 11.087 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.29..8.34 rows=1 width=570) (actual time=0.105..11.087 rows=1,578 loops=1)

14. 8.010 8.010 ↓ 1,578.0 1,578 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=558) (actual time=0.095..8.010 rows=1,578 loops=1)

15. 1.578 1.578 ↑ 1.0 1 1,578

Index Scan using pk_dim_organizations on dim_organizations sch (cost=0.29..8.31 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=1,578)

  • Index Cond: (organizationid = x.organizationid)
  • Filter: ((orglevel = 2) AND (x.administrationid = administrationid))
16. 1.578 1.578 ↑ 1.0 1 1,578

Index Scan using pk_dim_organizations on dim_organizations dst (cost=0.29..0.55 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1,578)

  • Index Cond: (organizationid = sch.parent_organizationid)
  • Filter: ((orglevel = 1) AND (sch.administrationid = administrationid))
17. 0.001 7.657 ↓ 0.0 0 1

Nested Loop (cost=0.96..10.64 rows=1 width=678) (actual time=7.657..7.657 rows=0 loops=1)

  • Join Filter: ((x_1.administrationid = psn_role.administrationid) AND (psn.personid = psn_role.personid))
18. 0.000 7.656 ↓ 0.0 0 1

Nested Loop (cost=0.82..10.34 rows=1 width=662) (actual time=7.656..7.656 rows=0 loops=1)

  • Join Filter: (sg.personid = psn.personid)
19. 0.001 7.656 ↓ 0.0 0 1

Nested Loop (cost=0.82..9.28 rows=1 width=617) (actual time=7.656..7.656 rows=0 loops=1)

  • Join Filter: ((x_1.administrationid = psg.administrationid) AND (sg.assessmentgroupid = psg.assessmentgroupid) AND (x_1.participantid = psg.participantid))
20. 0.272 7.655 ↓ 0.0 0 1

Nested Loop (cost=0.70..9.07 rows=1 width=629) (actual time=7.655..7.655 rows=0 loops=1)

  • Join Filter: (x_1.administrationid = sg.administrationid)
21. 1.087 5.805 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.57..8.90 rows=1 width=598) (actual time=0.019..5.805 rows=1,578 loops=1)

  • Join Filter: (x_1.administrationid = dst_1.administrationid)
22. 1.343 3.140 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.29..8.34 rows=1 width=594) (actual time=0.010..3.140 rows=1,578 loops=1)

23. 0.219 0.219 ↓ 1,578.0 1,578 1

CTE Scan on scores_info x_1 (cost=0.00..0.02 rows=1 width=566) (actual time=0.001..0.219 rows=1,578 loops=1)

24. 1.578 1.578 ↑ 1.0 1 1,578

Index Scan using pk_dim_organizations on dim_organizations sch_1 (cost=0.29..8.31 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=1,578)

  • Index Cond: (organizationid = x_1.organizationid)
  • Filter: ((orglevel = 2) AND (x_1.administrationid = administrationid))
25. 1.578 1.578 ↑ 1.0 1 1,578

Index Scan using pk_dim_organizations on dim_organizations dst_1 (cost=0.29..0.55 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1,578)

  • Index Cond: (organizationid = sch_1.parent_organizationid)
  • Filter: ((orglevel = 1) AND (sch_1.administrationid = administrationid))
26. 1.578 1.578 ↓ 0.0 0 1,578

Index Scan using udx_dim_assessmentgroup on dim_assessmentgroup sg (cost=0.12..0.15 rows=1 width=31) (actual time=0.001..0.001 rows=0 loops=1,578)

  • Index Cond: (administrationid = sch_1.administrationid)
  • Filter: (sch_1.organizationid = organizationid)
  • Rows Removed by Filter: 1
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_dim_participantassessmentgroup on dim_participantassessmentgroup psg (cost=0.12..0.19 rows=1 width=20) (never executed)

  • Index Cond: (administrationid = sch_1.administrationid)
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on dim_person psn (cost=0.00..1.03 rows=3 width=45) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dim_organizationpersonrole on dim_organizationpersonrole psn_role (cost=0.13..0.27 rows=1 width=20) (never executed)

  • Index Cond: ((administrationid = sch_1.administrationid) AND (organizationid = sch_1.organizationid))
  • Filter: (((roleid)::text = ANY ('{Teacher,"Test Administrator"}'::text[])) OR (orglevel = 3))
Planning time : 15.767 ms
Execution time : 34.033 ms