explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 29vu

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 0.519 ↓ 4.0 4 1

Nested Loop (cost=28.47..37.02 rows=1 width=1,250) (actual time=0.230..0.519 rows=4 loops=1)

2.          

CTE docinfo

3. 0.023 0.023 ↓ 4.0 4 1

Index Scan using udx_dim_assessmentparticipant on dim_assessmentparticipant d (cost=0.43..12.02 rows=1 width=170) (actual time=0.016..0.023 rows=4 loops=1)

  • Index Cond: ((administrationid = 553,230) AND (studentidcode = 502,225,171))
  • Filter: (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[]))
4. 0.028 0.028 ↓ 4.0 4 1

CTE Scan on docinfo x (cost=0.00..0.02 rows=1 width=166) (actual time=0.018..0.028 rows=4 loops=1)

5. 0.004 0.476 ↑ 1.0 1 4

Nested Loop (cost=16.45..24.76 rows=1 width=1,182) (actual time=0.118..0.119 rows=1 loops=4)

6. 0.008 0.456 ↑ 1.0 1 4

Nested Loop (cost=16.17..24.23 rows=1 width=1,172) (actual time=0.113..0.114 rows=1 loops=4)

  • Join Filter: (x.administrationid = sch.administrationid)
7. 0.004 0.436 ↑ 1.0 1 4

Subquery Scan on z (cost=15.89..15.92 rows=1 width=1,158) (actual time=0.109..0.109 rows=1 loops=4)

  • Filter: ((z.attemptstatus <> 'N'::text) OR (COALESCE(NULLIF((x.invalidationstatus)::text, ''::text), '*'::text) <> 'NLE'::text))
8. 0.064 0.432 ↑ 1.0 1 4

Aggregate (cost=15.89..15.90 rows=1 width=1,190) (actual time=0.108..0.108 rows=1 loops=4)

9. 0.031 0.368 ↓ 8.0 8 4

Nested Loop (cost=1.12..15.71 rows=1 width=54) (actual time=0.024..0.092 rows=8 loops=4)

10. 0.027 0.296 ↓ 10.0 10 4

Nested Loop (cost=0.99..15.54 rows=1 width=36) (actual time=0.021..0.074 rows=10 loops=4)

  • Join Filter: (s.assessmentformid = frm.assessmentformid)
11. 0.015 0.228 ↓ 10.0 10 4

Nested Loop (cost=0.84..15.36 rows=1 width=37) (actual time=0.018..0.057 rows=10 loops=4)

  • Join Filter: (s.assessmentlevelid = lvl.assessmentlevelid)
  • Rows Removed by Join Filter: 44
12. 0.039 0.172 ↓ 10.0 10 4

Nested Loop (cost=0.84..13.98 rows=1 width=38) (actual time=0.016..0.043 rows=10 loops=4)

13. 0.048 0.092 ↓ 10.0 10 4

Nested Loop (cost=0.56..9.67 rows=1 width=34) (actual time=0.012..0.023 rows=10 loops=4)

  • Join Filter: (s.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 16
14. 0.044 0.044 ↓ 10.0 10 4

Index Scan using idx_fact_subtestobjectiveresult_1 on fact_subtestobjectiveresult s (cost=0.56..8.58 rows=1 width=32) (actual time=0.009..0.011 rows=10 loops=4)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = x.participantid) AND (assessmentparticipantid = x.assessmentparticipantid))
15. 0.000 0.000 ↑ 1.3 3 41

Seq Scan on dim_subtest tst (cost=0.00..1.04 rows=4 width=10) (actual time=0.000..0.000 rows=3 loops=41)

16. 0.041 0.041 ↑ 1.0 1 41

Index Only Scan using pk_dim_scoresetform on dim_scoresetform sfm (cost=0.28..4.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=41)

  • Index Cond: ((administrationid = x.administrationid) AND (assessmentformid = s.assessmentformid) AND (scoresetid = s.scoresetid))
  • Heap Fetches: 0
17. 0.041 0.041 ↑ 3.4 5 41

Seq Scan on dim_assessmentlevel lvl (cost=0.00..1.17 rows=17 width=7) (actual time=0.000..0.001 rows=5 loops=41)

18. 0.041 0.041 ↑ 1.0 1 41

Index Scan using pk_dim_assessmentform on dim_assessmentform frm (cost=0.14..0.17 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=41)

  • Index Cond: (assessmentformid = sfm.assessmentformid)
19. 0.041 0.041 ↑ 1.0 1 41

Index Scan using pk_dim_scoreset on dim_scoreset sset (cost=0.14..0.17 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=41)

  • Index Cond: (scoresetid = sfm.scoresetid)
  • Filter: ((scoresetcategory)::text = ANY ('{SUBJECT,OBJECTIVE}'::text[]))
  • Rows Removed by Filter: 0
20. 0.012 0.012 ↑ 1.0 1 4

Index Scan using pk_dim_organizations on dim_organizations sch (cost=0.28..8.30 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=4)

  • Index Cond: (organizationid = z.organizationid)
21. 0.016 0.016 ↑ 1.0 1 4

Index Scan using idx_dim_organizations_1 on dim_organizations dst (cost=0.28..0.53 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=4)

  • Index Cond: ((administrationid = sch.administrationid) AND (organizationid = sch.parent_organizationid))
  • Filter: (orglevel = 1)
Planning time : 3.469 ms
Execution time : 0.973 ms