explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i2Fu

Settings
# exclusive inclusive rows x rows loops node
1. 1.074 156.218 ↓ 997.0 997 1

Nested Loop (cost=35.93..11,310.08 rows=1 width=488) (actual time=123.418..156.218 rows=997 loops=1)

  • Join Filter: ((x.administrationid = ofrm.administrationid) AND (frm.assessmentformid = ofrm.assessmentformid) AND (sset.scoresetid = ofrm.scoresetid))
  • Rows Removed by Join Filter: 4,009
2. 0.540 140.189 ↓ 997.0 997 1

Nested Loop (cost=35.65..11,309.42 rows=1 width=512) (actual time=114.811..140.189 rows=997 loops=1)

  • Join Filter: (tst.subtestid = sset.subtestid)
  • Rows Removed by Join Filter: 1,981
3. 0.971 136.649 ↓ 1,000.0 1,000 1

Nested Loop (cost=35.51..11,308.90 rows=1 width=514) (actual time=114.792..136.649 rows=1,000 loops=1)

  • Join Filter: ((x.administrationid = tst.administrationid) AND (lvl.subtestid = tst.subtestid))
  • Rows Removed by Join Filter: 2,955
4. 6.033 133.678 ↓ 1,000.0 1,000 1

Nested Loop (cost=35.38..11,308.64 rows=1 width=506) (actual time=113.966..133.678 rows=1,000 loops=1)

  • Join Filter: ((x.administrationid = frm.administrationid) AND (lvl.assessmentlevelid = frm.assessmentlevelid))
  • Rows Removed by Join Filter: 16,000
5. 5.928 110.645 ↓ 1,000.0 17,000 1

Nested Loop (cost=35.24..11,305.58 rows=17 width=498) (actual time=13.706..110.645 rows=17,000 loops=1)

  • Join Filter: (x.administrationid = lvl.administrationid)
6. 1.000 1.000 ↑ 1.0 17 1

Index Scan using udx_dim_assessmentlevel on dim_assessmentlevel lvl (cost=0.14..12.39 rows=17 width=12) (actual time=0.990..1.000 rows=17 loops=1)

7. 1.235 103.717 ↓ 1,000.0 1,000 17

Materialize (cost=35.10..11,292.94 rows=1 width=486) (actual time=0.748..6.101 rows=1,000 loops=17)

8. 1.048 102.482 ↓ 1,000.0 1,000 1

Nested Loop (cost=35.10..11,292.93 rows=1 width=486) (actual time=12.707..102.482 rows=1,000 loops=1)

  • Join Filter: (x.administrationid = org.administrationid)
9. 0.340 87.434 ↓ 1,000.0 1,000 1

Nested Loop (cost=34.82..11,292.59 rows=1 width=482) (actual time=9.543..87.434 rows=1,000 loops=1)

  • Join Filter: (x.administrationid = ses.administrationid)
10. 0.564 76.094 ↓ 1,000.0 1,000 1

Nested Loop (cost=34.53..11,292.26 rows=1 width=478) (actual time=7.050..76.094 rows=1,000 loops=1)

11. 1.393 1.530 ↓ 1.1 1,000 1

Bitmap Heap Scan on stg_subtestobjective_score x (cost=34.10..3,668.13 rows=943 width=470) (actual time=0.220..1.530 rows=1,000 loops=1)

  • Recheck Cond: ((row_num >= 1) AND (row_num <= 1,000))
  • Heap Blocks: exact=778
12. 0.137 0.137 ↓ 1.1 1,000 1

Bitmap Index Scan on idx_stg_subtestobjective_score_1 (cost=0.00..33.87 rows=943 width=0) (actual time=0.137..0.137 rows=1,000 loops=1)

  • Index Cond: ((row_num >= 1) AND (row_num <= 1,000))
13. 74.000 74.000 ↑ 1.0 1 1,000

Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt (cost=0.43..8.07 rows=1 width=32) (actual time=0.074..0.074 rows=1 loops=1,000)

  • Index Cond: ((administrationid = x.administrationid) AND (documentidcode = x.documentidcode))
  • Filter: ((x.studentidcode = studentidcode) AND (x.testeventidcode = testeventidcode))
14. 11.000 11.000 ↑ 1.0 1 1,000

Index Scan using pk_dim_assessmentsession on dim_assessmentsession ses (cost=0.29..0.31 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1,000)

  • Index Cond: (assessmentsessionid = asmt.assessmentsessionid)
  • Filter: (asmt.administrationid = administrationid)
15. 14.000 14.000 ↑ 1.0 1 1,000

Index Scan using idx_dim_organizations_2 on dim_organizations org (cost=0.28..0.33 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1,000)

  • Index Cond: ((administrationid = ses.administrationid) AND (organizationid = ses.organizationid))
  • Filter: (orglevel = 2)
16. 17.000 17.000 ↑ 1.0 1 17,000

Index Scan using pk_dim_assessmentform on dim_assessmentform frm (cost=0.14..0.17 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=17,000)

  • Index Cond: (assessmentformid = asmt.assessmentformid)
  • Filter: (asmt.administrationid = administrationid)
17. 2.000 2.000 ↑ 1.0 4 1,000

Index Scan using udx_dim_subtest on dim_subtest tst (cost=0.13..0.20 rows=4 width=8) (actual time=0.002..0.002 rows=4 loops=1,000)

  • Index Cond: (administrationid = asmt.administrationid)
18. 3.000 3.000 ↑ 1.0 3 1,000

Index Scan using udx_dim_scoreset on dim_scoreset sset (cost=0.14..0.48 rows=3 width=17) (actual time=0.002..0.003 rows=3 loops=1,000)

  • Index Cond: ((administrationid = x.administrationid) AND ((scoresetcode)::text = (x.scoresetcode)::text))
19. 14.955 14.955 ↑ 2.0 5 997

Index Only Scan using pk_dim_scoresetform on dim_scoresetform ofrm (cost=0.28..0.48 rows=10 width=12) (actual time=0.007..0.015 rows=5 loops=997)

  • Index Cond: ((administrationid = asmt.administrationid) AND (assessmentformid = asmt.assessmentformid))
  • Heap Fetches: 5,006
Planning time : 89.292 ms
Execution time : 156.471 ms