explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fTvo

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 0.535 ↓ 4.0 4 1

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

2.          

CTE docinfo

3. 0.029 0.029 ↓ 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.023..0.029 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.034 0.034 ↓ 4.0 4 1

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

5. 0.004 0.484 ↑ 1.0 1 4

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

6. 0.004 0.464 ↑ 1.0 1 4

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

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

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

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

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

9. 0.031 0.376 ↓ 8.0 8 4

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

10. 0.027 0.304 ↓ 10.0 10 4

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

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

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

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

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

13. 0.052 0.096 ↓ 10.0 10 4

Nested Loop (cost=0.56..9.67 rows=1 width=34) (actual time=0.012..0.024 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.016 0.016 ↑ 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.004..0.004 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))
Planning time : 3.419 ms
Execution time : 1.038 ms