explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sSWx

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 13.118 ↑ 1.0 100 1

Limit (cost=28,795.52..30,418.78 rows=100 width=1,218) (actual time=0.498..13.118 rows=100 loops=1)

2.          

CTE docinfo

3. 1.817 2.316 ↑ 108.2 100 1

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

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.499 0.499 ↑ 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.009..0.499 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.308 13.100 ↑ 107.6 100 1

Nested Loop (cost=15.89..174,710.82 rows=10,762 width=1,218) (actual time=0.497..13.100 rows=100 loops=1)

6. 0.692 0.692 ↑ 108.2 100 1

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

7. 0.100 12.100 ↑ 1.0 1 100

Subquery Scan on z (cost=15.89..15.92 rows=1 width=1,154) (actual time=0.121..0.121 rows=1 loops=100)

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

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

9. 0.935 10.600 ↓ 8.0 8 100

Nested Loop (cost=1.12..15.71 rows=1 width=50) (actual time=0.019..0.106 rows=8 loops=100)

10. 0.835 8.700 ↓ 10.0 10 100

Nested Loop (cost=0.99..15.54 rows=1 width=32) (actual time=0.017..0.087 rows=10 loops=100)

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

Nested Loop (cost=0.84..15.36 rows=1 width=33) (actual time=0.015..0.069 rows=10 loops=100)

  • Join Filter: (s.assessmentlevelid = lvl.assessmentlevelid)
  • Rows Removed by Join Filter: 98
12. 0.270 4.700 ↓ 10.0 10 100

Nested Loop (cost=0.84..13.98 rows=1 width=34) (actual time=0.013..0.047 rows=10 loops=100)

13. 0.435 2.500 ↓ 10.0 10 100

Nested Loop (cost=0.56..9.67 rows=1 width=30) (actual time=0.010..0.025 rows=10 loops=100)

  • Join Filter: (s.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 27
14. 1.100 1.100 ↓ 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.008..0.011 rows=10 loops=100)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = x.participantid) AND (assessmentparticipantid = x.assessmentparticipantid))
15. 0.965 0.965 ↑ 1.0 4 965

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

16. 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
17. 0.965 0.965 ↑ 1.5 11 965

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

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=11) (actual time=0.001..0.001 rows=1 loops=965)

  • Index Cond: (assessmentformid = sfm.assessmentformid)
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=30) (actual time=0.001..0.001 rows=1 loops=965)

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