explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zxfh

Settings
# exclusive inclusive rows x rows loops node
1. 2,209.980 11,263.323 ↓ 3,186,049.0 3,186,049 1

Nested Loop (cost=1,001.26..12,977.94 rows=1 width=203) (actual time=0.680..11,263.323 rows=3,186,049 loops=1)

  • Join Filter: (asmt.participantid = stu.participantid)
2. 0.000 2,681.245 ↓ 3,186,049.0 3,186,049 1

Gather (cost=1,000.97..12,977.60 rows=1 width=106) (actual time=0.670..2,681.245 rows=3,186,049 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 1,358.543 5,665.142 ↓ 1,062,016.0 1,062,016 3 / 3

Nested Loop (cost=0.97..11,977.50 rows=1 width=106) (actual time=0.087..5,665.142 rows=1,062,016 loops=3)

4. 344.173 1,120.550 ↓ 1,062,016.0 1,062,016 3 / 3

Nested Loop (cost=0.56..11,977.05 rows=1 width=94) (actual time=0.076..1,120.550 rows=1,062,016 loops=3)

5. 42.875 42.875 ↓ 80.1 52,393 3 / 3

Parallel Seq Scan on dim_assessmentparticipant asmt (cost=0.00..6,412.48 rows=654 width=36) (actual time=0.055..42.875 rows=52,393 loops=3)

  • Filter: ((administrationid = 593,218) AND ((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
  • Rows Removed by Filter: 957
6. 733.502 733.502 ↓ 20.0 20 157,179 / 3

Index Scan using idx_fact_itemresult_1 on fact_itemresult x (cost=0.56..8.50 rows=1 width=78) (actual time=0.005..0.014 rows=20 loops=157,179)

  • Index Cond: ((administrationid = 593,218) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
7. 3,186.049 3,186.049 ↑ 1.0 1 3,186,049 / 3

Index Scan using udx_mv_metadata_1 on mv_metadata mv (cost=0.42..0.45 rows=1 width=96) (actual time=0.003..0.003 rows=1 loops=3,186,049)

  • Index Cond: ((administrationid = 593,218) AND (subtestid = x.subtestid) AND (assessmentlevelid = x.assessmentlevelid) AND (assessmentformid = x.assessmentformid) AND (objectiveid = x.objectiveid) AND (assessmentitemid = x.assessmentitemid))
8. 6,372.098 6,372.098 ↑ 1.0 1 3,186,049

Index Scan using pk_dim_participant on dim_participant stu (cost=0.29..0.31 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=3,186,049)

  • Index Cond: (participantid = x.participantid)
  • Filter: (administrationid = 593,218)
Planning time : 1.853 ms
Execution time : 11,420.548 ms