explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5cr5

Settings
# exclusive inclusive rows x rows loops node
1. 0.579 2.955 ↓ 361.0 361 1

Nested Loop (cost=44.35..49.59 rows=1 width=720) (actual time=1.923..2.955 rows=361 loops=1)

  • Join Filter: (x.administrationid = dst.administrationid)
2.          

CTE scores_info

3. 0.481 1.500 ↓ 361.0 361 1

Nested Loop (cost=1.39..44.18 rows=1 width=192) (actual time=0.039..1.500 rows=361 loops=1)

4. 0.075 0.297 ↓ 361.0 361 1

Nested Loop (cost=0.98..43.70 rows=1 width=78) (actual time=0.027..0.297 rows=361 loops=1)

5. 0.027 0.027 ↓ 13.0 13 1

Index Scan using udx_dim_assessmentparticipant on dim_assessmentparticipant asmt (cost=0.42..35.11 rows=1 width=36) (actual time=0.013..0.027 rows=13 loops=1)

  • Index Cond: ((administrationid = 593,218) AND (studentidcode = 2,673,617))
  • Filter: (((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
6. 0.195 0.195 ↓ 28.0 28 13

Index Scan using idx_fact_itemresult_1 on fact_itemresult x_1 (cost=0.56..8.58 rows=1 width=78) (actual time=0.005..0.015 rows=28 loops=13)

  • Index Cond: ((administrationid = 593,218) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
7. 0.722 0.722 ↑ 1.0 1 361

Index Scan using udx_mv_metadata_1 on mv_metadata mv (cost=0.42..0.45 rows=1 width=101) (actual time=0.002..0.002 rows=1 loops=361)

  • Index Cond: ((administrationid = 593,218) AND (subtestid = x_1.subtestid) AND (assessmentlevelid = x_1.assessmentlevelid) AND (assessmentformid = x_1.assessmentformid) AND (objectiveid = x_1.objectiveid) AND (assessmentitemid = x_1.assessmentitemid))
8. 0.109 2.015 ↓ 361.0 361 1

Hash Join (cost=0.04..5.04 rows=1 width=544) (actual time=1.904..2.015 rows=361 loops=1)

  • Hash Cond: ((sch.administrationid = x.administrationid) AND (sch.organizationid = x.organizationid))
9. 0.016 0.016 ↑ 1.0 114 1

Seq Scan on dim_organizations sch (cost=0.00..4.14 rows=114 width=24) (actual time=0.004..0.016 rows=114 loops=1)

10. 0.111 1.890 ↓ 361.0 361 1

Hash (cost=0.02..0.02 rows=1 width=536) (actual time=1.890..1.890 rows=361 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 61kB
11. 1.779 1.779 ↓ 361.0 361 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=536) (actual time=0.043..1.779 rows=361 loops=1)

12. 0.361 0.361 ↑ 1.0 1 361

Index Scan using pk_dim_organizations on dim_organizations dst (cost=0.14..0.34 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=361)

  • Index Cond: (organizationid = sch.parent_organizationid)
  • Filter: (sch.administrationid = administrationid)
Planning time : 1.583 ms
Execution time : 3.057 ms