explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ci5b

Settings
# exclusive inclusive rows x rows loops node
1. 0.634 578.634 ↓ 789.0 1,578 1

Unique (cost=419.66..419.76 rows=2 width=678) (actual time=577.927..578.634 rows=1,578 loops=1)

2.          

CTE scores_info

3. 3.859 542.902 ↓ 1,578.0 1,578 1

Nested Loop (cost=1.95..400.05 rows=1 width=201) (actual time=0.212..542.902 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.908 2.523 ↓ 1,578.0 1,578 1

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

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

Nested Loop (cost=0.84..25.82 rows=1 width=58) (actual time=0.041..0.175 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.015..0.016 rows=1 loops=1)

  • Index Cond: ((administrationid = 592030) AND (studentidcode = 104575))
7. 0.142 0.142 ↓ 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.024..0.142 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.440 1.440 ↓ 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.020..0.072 rows=79 loops=20)

  • Index Cond: ((administrationid = 592030) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
9. 536.520 536.520 ↑ 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.208..0.340 rows=1 loops=1,578)

  • Index Cond: ((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. 10.465 578.000 ↓ 789.0 1,578 1

Sort (cost=19.60..19.61 rows=2 width=678) (actual time=577.925..578.000 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.249 567.535 ↓ 789.0 1,578 1

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

12. 5.331 559.753 ↓ 1,578.0 1,578 1

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

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

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

14. 545.591 545.591 ↓ 1,578.0 1,578 1

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

15. 3.156 3.156 ↑ 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.002..0.002 rows=1 loops=1,578)

  • Index Cond: (organizationid = x.organizationid)
  • Filter: ((orglevel = 2) AND (x.administrationid = administrationid))
16. 3.156 3.156 ↑ 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.002..0.002 rows=1 loops=1,578)

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

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

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

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

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

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

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

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

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

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

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

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

23. 0.222 0.222 ↓ 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.222 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.332 ms
Execution time : 579.078 ms