explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bPXx

Settings
# exclusive inclusive rows x rows loops node
1. 0.476 2.330 ↓ 261.0 261 1

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

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

CTE scores_info

3. 0.373 1.169 ↓ 261.0 261 1

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

4. 0.074 0.274 ↓ 261.0 261 1

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

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

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

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

  • Index Cond: ((administrationid = 593,218) AND (studentidcode = 2,673,617))
7. 0.030 0.030 ↓ 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.020..0.030 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.084 1.593 ↓ 261.0 261 1

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

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

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

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

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

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

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

14. 0.261 0.261 ↑ 1.0 1 261

Index Scan using idx_dim_organizations_2 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: ((administrationid = sch.administrationid) AND (orglevel = 1) AND (organizationid = sch.parent_organizationid))
Planning time : 2.557 ms
Execution time : 2.484 ms