explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4eNJ

Settings
# exclusive inclusive rows x rows loops node
1. 6,711.354 26,751.063 ↓ 3,186,049.0 3,186,049 1

Nested Loop (cost=12,978.11..12,983.35 rows=1 width=678) (actual time=15,419.360..26,751.063 rows=3,186,049 loops=1)

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

CTE scores_info

3. 1,870.502 10,657.065 ↓ 3,186,049.0 3,186,049 1

Nested Loop (cost=1,001.26..12,977.93 rows=1 width=203) (actual time=0.832..10,657.065 rows=3,186,049 loops=1)

4. 0.000 2,414.465 ↓ 3,186,049.0 3,186,049 1

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

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

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

6. 337.692 1,114.582 ↓ 1,062,016.0 1,062,016 3 / 3

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

7. 43.388 43.388 ↓ 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.059..43.388 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
8. 733.502 733.502 ↓ 20.0 20 157,179 / 3

Index Scan using idx_fact_itemresult_1 on fact_itemresult x_1 (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))
9. 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_1.subtestid) AND (assessmentlevelid = x_1.assessmentlevelid) AND (assessmentformid = x_1.assessmentformid) AND (objectiveid = x_1.objectiveid) AND (assessmentitemid = x_1.assessmentitemid))
10. 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_1.participantid)
  • Filter: (administrationid = 593,218)
11. 1,434.371 16,853.660 ↓ 3,186,049.0 3,186,049 1

Hash Join (cost=0.04..5.04 rows=1 width=566) (actual time=15,419.332..16,853.660 rows=3,186,049 loops=1)

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

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

13. 1,550.597 15,419.257 ↓ 3,186,049.0 3,186,049 1

Hash (cost=0.02..0.02 rows=1 width=558) (actual time=15,419.257..15,419.257 rows=3,186,049 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 160,144kB
14. 13,868.660 13,868.660 ↓ 3,186,049.0 3,186,049 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=558) (actual time=0.836..13,868.660 rows=3,186,049 loops=1)

15. 3,186.049 3,186.049 ↑ 1.0 1 3,186,049

Index Scan using idx_dim_organizations_2 on dim_organizations dst (cost=0.14..0.34 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=3,186,049)

  • Index Cond: ((administrationid = sch.administrationid) AND (organizationid = sch.parent_organizationid))
Planning time : 2.983 ms
Execution time : 27,015.248 ms