explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e8dw

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 0.935 ↓ 4.0 4 1

Nested Loop (cost=44.46..53.14 rows=1 width=1,282) (actual time=0.400..0.935 rows=4 loops=1)

2.          

CTE docinfo

3. 0.014 0.049 ↓ 4.0 4 1

Nested Loop Left Join (cost=4.63..25.71 rows=1 width=61) (actual time=0.033..0.049 rows=4 loops=1)

  • Join Filter: ((d.administrationid = g.administrationid) AND (g.participantid = d.participantid))
4. 0.023 0.023 ↓ 4.0 4 1

Index Scan using idx_dim_assessmentparticipant_1 on dim_assessmentparticipant d (cost=0.43..12.00 rows=1 width=21) (actual time=0.018..0.023 rows=4 loops=1)

  • Index Cond: ((administrationid = 553,230) AND (participantid = 472,099))
  • Filter: (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[]))
5. 0.004 0.012 ↓ 0.0 0 4

Bitmap Heap Scan on dim_participantrostergroup g (cost=4.20..13.68 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=4)

  • Recheck Cond: (administrationid = 553,230)
  • Filter: (participantid = 472,099)
6. 0.008 0.008 ↓ 0.0 0 4

Bitmap Index Scan on pk_dim_dim_participantrostergroup (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=4)

  • Index Cond: (administrationid = 553,230)
7. 0.056 0.056 ↓ 4.0 4 1

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

8. 0.004 0.852 ↑ 1.0 1 4

Nested Loop (cost=18.74..27.19 rows=1 width=1,182) (actual time=0.213..0.213 rows=1 loops=4)

9. 0.012 0.828 ↑ 1.0 1 4

Nested Loop (cost=18.46..26.52 rows=1 width=1,172) (actual time=0.206..0.207 rows=1 loops=4)

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

Subquery Scan on z (cost=18.18..18.21 rows=1 width=1,158) (actual time=0.200..0.200 rows=1 loops=4)

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

Aggregate (cost=18.18..18.19 rows=1 width=1,190) (actual time=0.199..0.199 rows=1 loops=4)

12. 0.028 0.700 ↓ 7.0 7 4

Nested Loop (cost=1.82..18.00 rows=1 width=63) (actual time=0.051..0.175 rows=7 loops=4)

13. 0.040 0.616 ↓ 7.0 7 4

Nested Loop (cost=1.68..17.84 rows=1 width=68) (actual time=0.046..0.154 rows=7 loops=4)

14. 0.034 0.492 ↓ 7.0 7 4

Nested Loop (cost=1.40..17.53 rows=1 width=72) (actual time=0.038..0.123 rows=7 loops=4)

15. 0.026 0.376 ↓ 10.0 10 4

Nested Loop (cost=1.25..17.37 rows=1 width=33) (actual time=0.033..0.094 rows=10 loops=4)

16. 0.030 0.268 ↓ 10.0 10 4

Nested Loop (cost=1.12..17.21 rows=1 width=35) (actual time=0.028..0.067 rows=10 loops=4)

17. 0.024 0.156 ↓ 10.0 10 4

Nested Loop (cost=0.99..17.04 rows=1 width=32) (actual time=0.023..0.039 rows=10 loops=4)

18. 0.032 0.032 ↑ 1.0 1 4

Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant doc (cost=0.43..8.45 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=4)

  • Index Cond: ((administrationid = x.administrationid) AND (documentidcode = x.documentidcode))
  • Filter: (studentidcode = x.studentidcode)
19. 0.100 0.100 ↓ 10.0 10 4

Index Scan using idx_fact_subtestobjectiveresult_1 on fact_subtestobjectiveresult s (cost=0.56..8.58 rows=1 width=40) (actual time=0.014..0.025 rows=10 loops=4)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = doc.participantid) AND (assessmentparticipantid = doc.assessmentparticipantid))
20. 0.082 0.082 ↑ 1.0 1 41

Index Scan using pk_dim_assessmentlevel on dim_assessmentlevel lvl (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=41)

  • Index Cond: (assessmentlevelid = s.assessmentlevelid)
  • Filter: ((administrationid = x.administrationid) AND (s.subtestid = subtestid))
21. 0.082 0.082 ↑ 1.0 1 41

Index Scan using pk_dim_subtest on dim_subtest tst (cost=0.13..0.15 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=41)

  • Index Cond: (subtestid = s.subtestid)
  • Filter: (administrationid = x.administrationid)
22. 0.082 0.082 ↑ 1.0 1 41

Index Scan using pk_dim_scoreset on dim_scoreset sset (cost=0.14..0.16 rows=1 width=43) (actual time=0.002..0.002 rows=1 loops=41)

  • Index Cond: (scoresetid = s.scoresetid)
  • Filter: (((scoresetcategory)::text = ANY ('{SUBJECT,OBJECTIVE}'::text[])) AND (administrationid = x.administrationid))
  • Rows Removed by Filter: 0
23. 0.084 0.084 ↑ 1.0 1 28

Index Only Scan using pk_dim_scoresetform on dim_scoresetform sfm (cost=0.28..0.30 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=28)

  • Index Cond: ((administrationid = x.administrationid) AND (assessmentformid = s.assessmentformid) AND (scoresetid = s.scoresetid))
  • Heap Fetches: 28
24. 0.056 0.056 ↑ 1.0 1 28

Index Scan using pk_dim_assessmentform on dim_assessmentform frm (cost=0.14..0.17 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=28)

  • Index Cond: (assessmentformid = s.assessmentformid)
  • Filter: (administrationid = x.administrationid)
25. 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)
26. 0.020 0.020 ↑ 1.0 1 4

Index Scan using idx_dim_organizations_2 on dim_organizations dst (cost=0.28..0.67 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: ((administrationid = sch.administrationid) AND (organizationid = sch.parent_organizationid))
Planning time : 125.216 ms
Execution time : 1.708 ms