explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y17N

Settings
# exclusive inclusive rows x rows loops node
1. 0.419 2.240 ↓ 261.0 261 1

Nested Loop (cost=52.94..58.42 rows=1 width=678) (actual time=1.505..2.240 rows=261 loops=1)

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

CTE scores_info

3. 0.387 1.168 ↓ 261.0 261 1

Nested Loop (cost=1.68..52.76 rows=1 width=203) (actual time=0.055..1.168 rows=261 loops=1)

4. 0.066 0.259 ↓ 261.0 261 1

Nested Loop (cost=1.26..52.28 rows=1 width=94) (actual time=0.044..0.259 rows=261 loops=1)

  • Join Filter: (stu.participantid = x_1.participantid)
5. 0.003 0.037 ↓ 13.0 13 1

Nested Loop (cost=0.71..43.77 rows=1 width=56) (actual time=0.025..0.037 rows=13 loops=1)

6. 0.008 0.008 ↑ 1.0 1 1

Index Scan using udx_dim_participant on dim_participant stu (cost=0.29..8.30 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: ((administrationid = 593,218) AND (studentidcode = 2,673,617))
7. 0.026 0.026 ↓ 13.0 13 1

Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt (cost=0.42..35.46 rows=1 width=36) (actual time=0.016..0.026 rows=13 loops=1)

  • Index Cond: ((administrationid = 593,218) AND (participantid = stu.participantid))
  • Filter: (((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
8. 0.156 0.156 ↓ 20.0 20 13

Index Scan using idx_fact_itemresult_1 on fact_itemresult x_1 (cost=0.56..8.50 rows=1 width=78) (actual time=0.004..0.012 rows=20 loops=13)

  • Index Cond: ((administrationid = 593,218) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
9. 0.522 0.522 ↑ 1.0 1 261

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

  • 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))
10. 0.080 1.560 ↓ 261.0 261 1

Hash Join (cost=0.04..5.26 rows=1 width=566) (actual time=1.490..1.560 rows=261 loops=1)

  • Hash Cond: ((sch.administrationid = x.administrationid) AND (sch.organizationid = x.organizationid))
11. 0.019 0.019 ↑ 1.0 106 1

Seq Scan on dim_organizations sch (cost=0.00..4.42 rows=106 width=24) (actual time=0.004..0.019 rows=106 loops=1)

  • Filter: (orglevel = 2)
  • Rows Removed by Filter: 8
12. 0.105 1.461 ↓ 261.0 261 1

Hash (cost=0.02..0.02 rows=1 width=558) (actual time=1.461..1.461 rows=261 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
13. 1.356 1.356 ↓ 261.0 261 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=558) (actual time=0.058..1.356 rows=261 loops=1)

14. 0.261 0.261 ↑ 1.0 1 261

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

  • Index Cond: (organizationid = sch.parent_organizationid)
  • Filter: ((orglevel = 1) AND (sch.administrationid = administrationid))
Planning time : 2.474 ms
Execution time : 2.386 ms