explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R0tE

Settings
# exclusive inclusive rows x rows loops node
1. 0.169 3.232 ↓ 100.0 100 1

Nested Loop (cost=6.66..1,178.82 rows=1 width=488) (actual time=0.118..3.232 rows=100 loops=1)

  • Join Filter: ((x.administrationid = ofrm.administrationid) AND (frm.assessmentformid = ofrm.assessmentformid) AND (sset.scoresetid = ofrm.scoresetid))
  • Rows Removed by Join Filter: 404
2. 0.222 2.663 ↓ 100.0 100 1

Nested Loop (cost=6.39..1,178.17 rows=1 width=512) (actual time=0.091..2.663 rows=100 loops=1)

  • Join Filter: ((x.administrationid = sset.administrationid) AND (tst.subtestid = sset.subtestid) AND ((x.scoresetcode)::text = (sset.scoresetcode)::text))
  • Rows Removed by Join Filter: 1,811
3. 0.102 2.241 ↓ 100.0 100 1

Nested Loop (cost=6.39..1,175.34 rows=1 width=514) (actual time=0.083..2.241 rows=100 loops=1)

  • Join Filter: ((x.administrationid = tst.administrationid) AND (lvl.subtestid = tst.subtestid))
  • Rows Removed by Join Filter: 100
4. 0.091 2.139 ↓ 100.0 100 1

Nested Loop (cost=6.39..1,174.24 rows=1 width=506) (actual time=0.081..2.139 rows=100 loops=1)

  • Join Filter: ((x.administrationid = lvl.administrationid) AND (frm.assessmentlevelid = lvl.assessmentlevelid))
5. 0.707 2.048 ↓ 100.0 100 1

Nested Loop (cost=6.39..1,172.82 rows=1 width=498) (actual time=0.079..2.048 rows=100 loops=1)

  • Join Filter: ((x.administrationid = frm.administrationid) AND (asmt.assessmentformid = frm.assessmentformid))
  • Rows Removed by Join Filter: 5,282
6. 0.054 0.941 ↓ 100.0 100 1

Nested Loop (cost=6.39..1,161.87 rows=1 width=486) (actual time=0.068..0.941 rows=100 loops=1)

  • Join Filter: (x.administrationid = org.administrationid)
7. 0.118 0.687 ↓ 100.0 100 1

Nested Loop (cost=6.11..1,161.53 rows=1 width=482) (actual time=0.059..0.687 rows=100 loops=1)

  • Join Filter: (x.administrationid = ses.administrationid)
8. 0.097 0.469 ↓ 100.0 100 1

Nested Loop (cost=5.82..1,161.20 rows=1 width=478) (actual time=0.046..0.469 rows=100 loops=1)

9. 0.153 0.172 ↓ 1.1 100 1

Bitmap Heap Scan on stg_subtestobjective_score x (cost=5.39..374.19 rows=93 width=470) (actual time=0.032..0.172 rows=100 loops=1)

  • Recheck Cond: ((row_num >= 1) AND (row_num <= 100))
  • Heap Blocks: exact=99
10. 0.019 0.019 ↓ 1.1 100 1

Bitmap Index Scan on idx_stg_subtestobjective_score_1 (cost=0.00..5.36 rows=93 width=0) (actual time=0.019..0.019 rows=100 loops=1)

  • Index Cond: ((row_num >= 1) AND (row_num <= 100))
11. 0.200 0.200 ↑ 1.0 1 100

Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt (cost=0.43..8.45 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: ((administrationid = x.administrationid) AND (documentidcode = x.documentidcode))
  • Filter: ((x.studentidcode = studentidcode) AND (x.testeventidcode = testeventidcode))
12. 0.100 0.100 ↑ 1.0 1 100

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

  • Index Cond: (assessmentsessionid = asmt.assessmentsessionid)
  • Filter: (asmt.administrationid = administrationid)
13. 0.200 0.200 ↑ 1.0 1 100

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

  • Index Cond: ((administrationid = ses.administrationid) AND (organizationid = ses.organizationid))
  • Filter: (orglevel = 2)
14. 0.400 0.400 ↑ 3.7 54 100

Seq Scan on dim_assessmentform frm (cost=0.00..7.98 rows=198 width=12) (actual time=0.001..0.004 rows=54 loops=100)

15. 0.000 0.000 ↑ 17.0 1 100

Seq Scan on dim_assessmentlevel lvl (cost=0.00..1.17 rows=17 width=12) (actual time=0.000..0.000 rows=1 loops=100)

16. 0.000 0.000 ↑ 2.0 2 100

Seq Scan on dim_subtest tst (cost=0.00..1.04 rows=4 width=8) (actual time=0.000..0.000 rows=2 loops=100)

17. 0.200 0.200 ↑ 1.6 19 100

Seq Scan on dim_scoreset sset (cost=0.00..2.30 rows=30 width=17) (actual time=0.000..0.002 rows=19 loops=100)

18. 0.400 0.400 ↑ 2.0 5 100

Index Only Scan using pk_dim_scoresetform on dim_scoresetform ofrm (cost=0.28..0.48 rows=10 width=12) (actual time=0.003..0.004 rows=5 loops=100)

  • Index Cond: ((administrationid = asmt.administrationid) AND (assessmentformid = asmt.assessmentformid))
  • Heap Fetches: 504
Planning time : 91.159 ms
Execution time : 3.414 ms