explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fWk6

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 3.590 ↓ 10.0 10 1

Nested Loop (cost=74.12..82.81 rows=1 width=1,282) (actual time=0.569..3.590 rows=10 loops=1)

2.          

CTE docinfo

3. 0.033 0.126 ↓ 14.0 14 1

Nested Loop Left Join (cost=4.63..55.03 rows=1 width=61) (actual time=0.043..0.126 rows=14 loops=1)

  • Join Filter: ((d.administrationid = g.administrationid) AND (g.participantid = d.participantid))
4. 0.065 0.065 ↓ 14.0 14 1

Index Scan using idx_dim_assessmentparticipant_1 on dim_assessmentparticipant d (cost=0.43..41.26 rows=1 width=21) (actual time=0.021..0.065 rows=14 loops=1)

  • Index Cond: ((administrationid = 553,230) AND (participantid = ANY ('{472099,703777,474386,482395}'::integer[])))
  • Filter: (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[]))
5. 0.014 0.028 ↓ 0.0 0 14

Bitmap Heap Scan on dim_participantrostergroup g (cost=4.20..13.67 rows=6 width=12) (actual time=0.002..0.002 rows=0 loops=14)

  • Recheck Cond: (administrationid = 553,230)
6. 0.014 0.014 ↓ 0.0 0 14

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

  • Index Cond: (administrationid = 553,230)
7. 0.140 0.140 ↓ 14.0 14 1

CTE Scan on docinfo x (cost=0.00..0.02 rows=1 width=166) (actual time=0.045..0.140 rows=14 loops=1)

8. 0.030 3.402 ↑ 1.0 1 14

Nested Loop (cost=19.09..27.54 rows=1 width=1,182) (actual time=0.242..0.243 rows=1 loops=14)

9. 0.012 3.332 ↑ 1.0 1 14

Nested Loop (cost=18.81..26.87 rows=1 width=1,172) (actual time=0.238..0.238 rows=1 loops=14)

  • Join Filter: (x.administrationid = sch.administrationid)
10. 0.014 3.290 ↑ 1.0 1 14

Subquery Scan on z (cost=18.53..18.56 rows=1 width=1,158) (actual time=0.234..0.235 rows=1 loops=14)

  • Filter: ((z.attemptstatus <> 'N'::text) OR (COALESCE(NULLIF((x.invalidationstatus)::text, ''::text), '*'::text) <> 'NLE'::text))
  • Rows Removed by Filter: 0
11. 0.252 3.276 ↑ 1.0 1 14

Aggregate (cost=18.53..18.54 rows=1 width=1,190) (actual time=0.233..0.234 rows=1 loops=14)

12. 0.088 3.024 ↓ 6.0 6 14

Nested Loop (cost=9.01..18.35 rows=1 width=63) (actual time=0.089..0.216 rows=6 loops=14)

13. 0.186 2.716 ↓ 8.0 8 14

Nested Loop (cost=8.87..18.18 rows=1 width=36) (actual time=0.068..0.194 rows=8 loops=14)

  • Join Filter: (s.scoresetid = sfm.scoresetid)
  • Rows Removed by Join Filter: 41
14. 0.114 1.540 ↓ 8.0 8 14

Nested Loop (cost=8.59..16.80 rows=1 width=44) (actual time=0.051..0.110 rows=8 loops=14)

  • Join Filter: (s.assessmentlevelid = lvl.assessmentlevelid)
  • Rows Removed by Join Filter: 29
15. 0.072 1.316 ↓ 8.0 8 14

Nested Loop (cost=8.59..15.42 rows=1 width=45) (actual time=0.049..0.094 rows=8 loops=14)

  • Join Filter: (s.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 15
16. 0.476 1.134 ↓ 8.0 8 14

Hash Join (cost=8.59..14.33 rows=1 width=43) (actual time=0.046..0.081 rows=8 loops=14)

  • Hash Cond: (frm.assessmentformid = s.assessmentformid)
17. 0.308 0.308 ↑ 1.0 198 14

Seq Scan on dim_assessmentform frm (cost=0.00..4.98 rows=198 width=11) (actual time=0.001..0.022 rows=198 loops=14)

18. 0.056 0.350 ↓ 8.0 8 14

Hash (cost=8.58..8.58 rows=1 width=32) (actual time=0.025..0.025 rows=8 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.294 0.294 ↓ 8.0 8 14

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.021 rows=8 loops=14)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = x.participantid) AND (assessmentparticipantid = x.assessmentparticipantid))
20. 0.110 0.110 ↑ 1.3 3 110

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

21. 0.110 0.110 ↑ 3.4 5 110

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

22. 0.990 0.990 ↑ 1.8 6 110

Index Only Scan using pk_dim_scoresetform on dim_scoresetform sfm (cost=0.28..1.25 rows=11 width=12) (actual time=0.005..0.009 rows=6 loops=110)

  • Index Cond: ((administrationid = x.administrationid) AND (assessmentformid = frm.assessmentformid))
  • Heap Fetches: 686
23. 0.220 0.220 ↑ 1.0 1 110

Index Scan using pk_dim_scoreset on dim_scoreset sset (cost=0.14..0.17 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=110)

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

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=10)

  • Index Cond: (organizationid = z.organizationid)
25. 0.040 0.040 ↑ 1.0 1 10

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

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