explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0aJW

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 15.642 ↑ 1.0 100 1

Limit (cost=28,795.39..30,405.43 rows=100 width=1,218) (actual time=0.529..15.642 rows=100 loops=1)

2.          

CTE docinfo

3. 2.288 2.790 ↑ 108.2 100 1

Gather (cost=1,000.00..28,779.63 rows=10,816 width=170) (actual time=0.349..2.790 rows=100 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.502 0.502 ↑ 7.0 640 3 / 3

Parallel Seq Scan on dim_assessmentparticipant d (cost=0.00..26,603.40 rows=4,507 width=170) (actual time=0.010..0.502 rows=640 loops=3)

  • Filter: ((administrationid = 553,230) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
  • Rows Removed by Filter: 1
5. 0.347 15.621 ↑ 107.6 100 1

Nested Loop (cost=15.76..173,288.67 rows=10,762 width=1,218) (actual time=0.529..15.621 rows=100 loops=1)

6. 0.774 0.774 ↑ 108.2 100 1

CTE Scan on docinfo x (cost=0.00..216.32 rows=10,816 width=166) (actual time=0.352..0.774 rows=100 loops=1)

7. 0.100 14.500 ↑ 1.0 1 100

Subquery Scan on z (cost=15.76..15.79 rows=1 width=1,154) (actual time=0.145..0.145 rows=1 loops=100)

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

Aggregate (cost=15.76..15.77 rows=1 width=1,190) (actual time=0.144..0.144 rows=1 loops=100)

9. 1.035 12.800 ↓ 8.0 8 100

Nested Loop (cost=1.25..15.58 rows=1 width=50) (actual time=0.023..0.128 rows=8 loops=100)

10. 1.135 10.800 ↓ 10.0 10 100

Nested Loop (cost=1.11..15.41 rows=1 width=36) (actual time=0.021..0.108 rows=10 loops=100)

  • Join Filter: (s.assessmentformid = frm.assessmentformid)
11. 0.470 8.700 ↓ 10.0 10 100

Nested Loop (cost=0.97..15.22 rows=1 width=37) (actual time=0.019..0.087 rows=10 loops=100)

12. 0.935 6.300 ↓ 10.0 10 100

Nested Loop (cost=0.69..10.92 rows=1 width=29) (actual time=0.016..0.063 rows=10 loops=100)

  • Join Filter: (s.subtestid = tst.subtestid)
13. 1.170 4.400 ↓ 10.0 10 100

Nested Loop (cost=0.56..10.05 rows=1 width=31) (actual time=0.014..0.044 rows=10 loops=100)

  • Join Filter: ((s.subtestid = lvl.subtestid) AND (s.assessmentlevelid = lvl.assessmentlevelid))
  • Rows Removed by Join Filter: 98
14. 1.300 1.300 ↓ 10.0 10 100

Index Scan using idx_fact_subtestobjectiveresult_1 on fact_subtestobjectiveresult s (cost=0.56..8.58 rows=1 width=28) (actual time=0.010..0.013 rows=10 loops=100)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = x.participantid) AND (assessmentparticipantid = x.assessmentparticipantid))
15. 1.930 1.930 ↑ 1.5 11 965

Seq Scan on dim_assessmentlevel lvl (cost=0.00..1.21 rows=17 width=15) (actual time=0.001..0.002 rows=11 loops=965)

  • Filter: (administrationid = x.administrationid)
16. 0.965 0.965 ↑ 1.0 1 965

Index Scan using pk_dim_subtest on dim_subtest tst (cost=0.13..0.86 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=965)

  • Index Cond: (subtestid = lvl.subtestid)
  • Filter: (administrationid = x.administrationid)
17. 1.930 1.930 ↑ 1.0 1 965

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

  • Index Cond: ((administrationid = x.administrationid) AND (assessmentformid = s.assessmentformid) AND (scoresetid = s.scoresetid))
  • Heap Fetches: 0
18. 0.965 0.965 ↑ 1.0 1 965

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

  • Index Cond: (assessmentformid = sfm.assessmentformid)
  • Filter: (administrationid = x.administrationid)
19. 0.965 0.965 ↑ 1.0 1 965

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

  • Index Cond: (scoresetid = sfm.scoresetid)
  • Filter: (((scoresetcategory)::text = ANY ('{SUBJECT,OBJECTIVE}'::text[])) AND (administrationid = x.administrationid))
  • Rows Removed by Filter: 0
Planning time : 5.089 ms
Execution time : 18.214 ms