explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lhB

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 7.668 ↓ 10.0 10 1

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

2.          

CTE docinfo

3. 0.033 0.131 ↓ 14.0 14 1

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

  • Join Filter: ((d.administrationid = g.administrationid) AND (g.participantid = d.participantid))
4. 0.070 0.070 ↓ 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.023..0.070 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.000 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.028 0.028 ↓ 0.0 0 14

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

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

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

8. 0.016 7.462 ↑ 1.0 1 14

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

9. 0.026 7.406 ↑ 1.0 1 14

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

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

Subquery Scan on z (cost=18.53..18.56 rows=1 width=1,158) (actual time=0.524..0.525 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 7.322 ↑ 1.0 1 14

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

12. 0.074 7.070 ↓ 6.0 6 14

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

13. 0.200 6.776 ↓ 8.0 8 14

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

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

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

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

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

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

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

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

Seq Scan on dim_assessmentform frm (cost=0.00..4.98 rows=198 width=11) (actual time=0.001..0.309 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.010..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.001..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 : 17.430 ms
Execution time : 8.383 ms