explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I2I1

Settings
# exclusive inclusive rows x rows loops node
1. 0.615 43,725.529 ↓ 789.0 1,578 1

Unique (cost=1,534,855.24..1,534,855.34 rows=2 width=678) (actual time=43,724.847..43,725.529 rows=1,578 loops=1)

2.          

CTE scores_info

3. 9.028 43,653.427 ↓ 1,578.0 1,578 1

Nested Loop (cost=1,009.29..1,534,835.64 rows=1 width=201) (actual time=37,016.740..43,653.427 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. 3,938.001 42,288.897 ↓ 1,578.0 1,578 1

Gather (cost=1,008.88..1,534,470.09 rows=1 width=96) (actual time=37,016.299..42,288.897 rows=1,578 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 16.358 38,350.896 ↓ 526.0 526 3

Nested Loop (cost=8.88..1,533,469.99 rows=1 width=96) (actual time=31,915.814..38,350.896 rows=526 loops=3)

  • Join Filter: (stu.participantid = asmt.participantid)
6. 6,761.935 38,334.508 ↓ 3.0 526 3

Hash Join (cost=8.45..1,533,387.92 rows=173 width=96) (actual time=31,905.879..38,334.508 rows=526 loops=3)

  • Hash Cond: (x_2.participantid = stu.participantid)
7. 31,572.552 31,572.552 ↑ 1.2 20,874,819 3

Parallel Seq Scan on fact_itemresult x_2 (cost=0.00..1,464,882.04 rows=26,093,523 width=78) (actual time=2.231..31,572.552 rows=20,874,819 loops=3)

  • Filter: (administrationid = 592030)
8. 0.004 0.021 ↑ 1.0 1 3

Hash (cost=8.44..8.44 rows=1 width=22) (actual time=0.021..0.021 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.017 0.017 ↑ 1.0 1 3

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=3)

  • Index Cond: ((administrationid = 592030) AND (studentidcode = 104575))
10. 0.030 0.030 ↑ 1.0 1 1,578

Index Scan using pk_dim_assessmentparticipant on dim_assessmentparticipant asmt (cost=0.42..0.46 rows=1 width=36) (actual time=0.030..0.030 rows=1 loops=1,578)

  • Index Cond: (assessmentparticipantid = x_2.assessmentparticipantid)
  • Filter: ((administrationid = 592030) AND ((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND ((testingstatus)::text = 'ReportingReady'::text) AND (x_2.participantid = participantid) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
11. 1,355.502 1,355.502 ↑ 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.528..0.859 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))
12. 13.044 43,724.914 ↓ 789.0 1,578 1

Sort (cost=19.60..19.61 rows=2 width=678) (actual time=43,724.846..43,724.914 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
13. 0.532 43,711.870 ↓ 789.0 1,578 1

Append (cost=0.57..19.59 rows=2 width=678) (actual time=37,016.807..43,711.870 rows=1,578 loops=1)

14. 23.968 43,703.254 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.57..8.93 rows=1 width=678) (actual time=37,016.806..43,703.254 rows=1,578 loops=1)

  • Join Filter: (x.administrationid = dst.administrationid)
15. 6.024 43,674.552 ↓ 1,578.0 1,578 1

Nested Loop (cost=0.29..8.34 rows=1 width=570) (actual time=37,016.770..43,674.552 rows=1,578 loops=1)

16. 43,657.482 43,657.482 ↓ 1,578.0 1,578 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=558) (actual time=37,016.749..43,657.482 rows=1,578 loops=1)

17. 11.046 11.046 ↑ 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.007..0.007 rows=1 loops=1,578)

  • Index Cond: (organizationid = x.organizationid)
  • Filter: ((orglevel = 2) AND (x.administrationid = administrationid))
18. 4.734 4.734 ↑ 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.003..0.003 rows=1 loops=1,578)

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

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

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

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

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

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

  • Join Filter: ((x_1.administrationid = psg.administrationid) AND (sg.assessmentgroupid = psg.assessmentgroupid) AND (x_1.participantid = psg.participantid))
22. 0.458 8.082 ↓ 0.0 0 1

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

  • Join Filter: (x_1.administrationid = sg.administrationid)
23. 1.225 6.046 ↓ 1,578.0 1,578 1

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

  • Join Filter: (x_1.administrationid = dst_1.administrationid)
24. 1.448 3.243 ↓ 1,578.0 1,578 1

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

25. 0.217 0.217 ↓ 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.217 rows=1,578 loops=1)

26. 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))
27. 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))
28. 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
29. 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
30. 0.000 0.000 ↓ 0.0 0

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

31. 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 : 14.900 ms
Execution time : 43,726.067 ms