explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qQSo

Settings
# exclusive inclusive rows x rows loops node
1. 5,089.768 18,941.255 ↓ 3,216,549.0 3,216,549 1

Nested Loop (cost=13,829.04..13,834.29 rows=1 width=720) (actual time=9,396.581..18,941.255 rows=3,216,549 loops=1)

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

CTE scores_info

3. 0.000 4,073.304 ↓ 3,216,549.0 3,216,549 1

Gather (cost=1,000.97..13,828.87 rows=1 width=192) (actual time=1.633..4,073.304 rows=3,216,549 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1,795.592 6,094.957 ↓ 1,072,183.0 1,072,183 3 / 3

Nested Loop (cost=0.97..12,828.77 rows=1 width=192) (actual time=0.400..6,094.957 rows=1,072,183 loops=3)

5. 307.919 1,082.816 ↓ 1,072,183.0 1,072,183 3 / 3

Nested Loop (cost=0.56..12,828.29 rows=1 width=78) (actual time=0.380..1,082.816 rows=1,072,183 loops=3)

6. 41.395 41.395 ↓ 80.0 52,393 3 / 3

Parallel Seq Scan on dim_assessmentparticipant asmt (cost=0.00..7,248.48 rows=655 width=36) (actual time=0.343..41.395 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
7. 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.51 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))
8. 3,216.549 3,216.549 ↑ 1.0 1 3,216,549 / 3

Index Scan using udx_mv_metadata_1 on mv_metadata mv (cost=0.42..0.45 rows=1 width=101) (actual time=0.003..0.003 rows=1 loops=3,216,549)

  • 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))
9. 1,238.428 10,634.938 ↓ 3,216,549.0 3,216,549 1

Hash Join (cost=0.04..5.04 rows=1 width=544) (actual time=9,396.550..10,634.938 rows=3,216,549 loops=1)

  • Hash Cond: ((sch.administrationid = x.administrationid) AND (sch.organizationid = x.organizationid))
10. 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.004..0.032 rows=114 loops=1)

11. 1,732.218 9,396.478 ↓ 3,216,549.0 3,216,549 1

Hash (cost=0.02..0.02 rows=1 width=536) (actual time=9,396.478..9,396.478 rows=3,216,549 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 157,653kB
12. 7,664.260 7,664.260 ↓ 3,216,549.0 3,216,549 1

CTE Scan on scores_info x (cost=0.00..0.02 rows=1 width=536) (actual time=1.638..7,664.260 rows=3,216,549 loops=1)

13. 3,216.549 3,216.549 ↑ 1.0 1 3,216,549

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=3,216,549)

  • Index Cond: (organizationid = sch.parent_organizationid)
  • Filter: (sch.administrationid = administrationid)
Planning time : 1.549 ms
Execution time : 19,134.358 ms