explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OOnF

Settings
# exclusive inclusive rows x rows loops node
1. 6,774.198 21,797.656 ↓ 3,216,549.0 3,216,549 1

Nested Loop (cost=13,829.04..13,834.29 rows=1 width=720) (actual time=10,356.642..21,797.656 rows=3,216,549 loops=1)

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

CTE scores_info

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

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

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

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

5. 285.795 1,116.524 ↓ 1,072,183.0 1,072,183 3 / 3

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

6. 44.834 44.834 ↓ 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.294..44.834 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. 785.895 785.895 ↓ 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.015 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,450.337 11,806.909 ↓ 3,216,549.0 3,216,549 1

Hash Join (cost=0.04..5.04 rows=1 width=544) (actual time=10,356.614..11,806.909 rows=3,216,549 loops=1)

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

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

11. 1,933.749 10,356.537 ↓ 3,216,549.0 3,216,549 1

Hash (cost=0.02..0.02 rows=1 width=536) (actual time=10,356.536..10,356.537 rows=3,216,549 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 157,653kB
12. 8,422.788 8,422.788 ↓ 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.506..8,422.788 rows=3,216,549 loops=1)

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

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

  • Index Cond: ((administrationid = sch.administrationid) AND (organizationid = sch.parent_organizationid))
Planning time : 1.710 ms
Execution time : 22,019.443 ms