explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kvpp

Settings
# exclusive inclusive rows x rows loops node
1. 2,928.456 123,044.826 ↓ 100.2 1,078,860 1

Nested Loop (cost=28,795.52..203,490.45 rows=10,762 width=1,218) (actual time=0.513..123,044.826 rows=1,078,860 loops=1)

2.          

CTE docinfo

3. 3,381.623 3,805.081 ↓ 99.7 1,078,860 1

Gather (cost=1,000.00..28,779.63 rows=10,816 width=170) (actual time=0.340..3,805.081 rows=1,078,860 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 423.458 423.458 ↓ 79.8 359,620 3 / 3

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

  • Filter: ((administrationid = 553,230) AND (COALESCE(NULLIF((nottestedcode)::text, ''::text), '*'::text) = ANY ('{*,DN}'::text[])))
  • Rows Removed by Filter: 919
5. 4,678.350 4,678.350 ↓ 99.7 1,078,860 1

CTE Scan on docinfo x (cost=0.00..216.32 rows=10,816 width=166) (actual time=0.342..4,678.350 rows=1,078,860 loops=1)

6. 0.000 115,438.020 ↑ 1.0 1 1,078,860

Subquery Scan on z (cost=15.89..15.92 rows=1 width=1,154) (actual time=0.107..0.107 rows=1 loops=1,078,860)

  • Filter: ((z.attemptstatus <> 'N'::text) OR (COALESCE(NULLIF((x.invalidationstatus)::text, ''::text), '*'::text) <> 'NLE'::text))
7. 14,025.180 115,438.020 ↑ 1.0 1 1,078,860

Aggregate (cost=15.89..15.90 rows=1 width=1,190) (actual time=0.107..0.107 rows=1 loops=1,078,860)

8. 6,471.337 101,412.840 ↓ 9.0 9 1,078,860

Nested Loop (cost=1.12..15.71 rows=1 width=50) (actual time=0.017..0.094 rows=9 loops=1,078,860)

9. 5,392.477 83,072.220 ↓ 11.0 11 1,078,860

Nested Loop (cost=0.99..15.54 rows=1 width=32) (actual time=0.016..0.077 rows=11 loops=1,078,860)

  • Join Filter: (s.assessmentformid = frm.assessmentformid)
10. 8,629.057 65,810.460 ↓ 11.0 11 1,078,860

Nested Loop (cost=0.84..15.36 rows=1 width=33) (actual time=0.014..0.061 rows=11 loops=1,078,860)

  • Join Filter: (s.assessmentlevelid = lvl.assessmentlevelid)
  • Rows Removed by Join Filter: 87
11. 8,629.057 45,312.120 ↓ 11.0 11 1,078,860

Nested Loop (cost=0.84..13.98 rows=1 width=34) (actual time=0.012..0.042 rows=11 loops=1,078,860)

12. 12,946.320 24,813.780 ↓ 11.0 11 1,078,860

Nested Loop (cost=0.56..9.67 rows=1 width=30) (actual time=0.010..0.023 rows=11 loops=1,078,860)

  • Join Filter: (s.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 19
13. 11,867.460 11,867.460 ↓ 11.0 11 1,078,860

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=11 loops=1,078,860)

  • Index Cond: ((administrationid = x.administrationid) AND (participantid = x.participantid) AND (assessmentparticipantid = x.assessmentparticipantid))
14. 0.000 0.000 ↑ 1.3 3 11,869,283

Seq Scan on dim_subtest tst (cost=0.00..1.04 rows=4 width=10) (actual time=0.000..0.000 rows=3 loops=11,869,283)

15. 11,869.283 11,869.283 ↑ 1.0 1 11,869,283

Index Only Scan using pk_dim_scoresetform on dim_scoresetform sfm (cost=0.28..4.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=11,869,283)

  • Index Cond: ((administrationid = x.administrationid) AND (assessmentformid = s.assessmentformid) AND (scoresetid = s.scoresetid))
  • Heap Fetches: 0
16. 11,869.283 11,869.283 ↑ 1.9 9 11,869,283

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

17. 11,869.283 11,869.283 ↑ 1.0 1 11,869,283

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=11,869,283)

  • Index Cond: (assessmentformid = sfm.assessmentformid)
18. 11,869.283 11,869.283 ↑ 1.0 1 11,869,283

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=11,869,283)

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