explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yqa

Settings
# exclusive inclusive rows x rows loops node
1. 39.071 2,101.421 ↓ 43,331.0 43,331 1

Nested Loop (cost=2,686.12..187,849.10 rows=1 width=488) (actual time=151.779..2,101.421 rows=43,331 loops=1)

  • Join Filter: ((x.administrationid = ofrm.administrationid) AND (frm.assessmentformid = ofrm.assessmentformid) AND (sset.scoresetid = ofrm.scoresetid))
  • Rows Removed by Join Filter: 212,211
2. 43.479 1,802.364 ↓ 43,331.0 43,331 1

Nested Loop (cost=2,685.85..187,848.45 rows=1 width=512) (actual time=151.737..1,802.364 rows=43,331 loops=1)

  • Join Filter: (x.administrationid = org.administrationid)
3. 83.985 1,672.223 ↓ 43,331.0 43,331 1

Nested Loop (cost=2,685.57..187,848.11 rows=1 width=508) (actual time=150.468..1,672.223 rows=43,331 loops=1)

  • Join Filter: ((x.administrationid = lvl.administrationid) AND (tst.subtestid = lvl.subtestid))
  • Rows Removed by Join Filter: 110,087
4. 115.629 1,434.820 ↓ 76,709.0 153,418 1

Nested Loop (cost=2,685.43..187,847.64 rows=2 width=508) (actual time=150.459..1,434.820 rows=153,418 loops=1)

  • Join Filter: (x.administrationid = frm.administrationid)
5. 394.914 1,165.773 ↓ 76,709.0 153,418 1

Nested Loop (cost=2,685.28..187,847.29 rows=2 width=496) (actual time=150.448..1,165.773 rows=153,418 loops=1)

  • Join Filter: ((tst.administrationid = x.administrationid) AND ((sset.scoresetcode)::text = (x.scoresetcode)::text))
  • Rows Removed by Join Filter: 1,996,582
6. 0.097 0.170 ↓ 1.4 43 1

Merge Join (cost=4.12..4.67 rows=30 width=25) (actual time=0.041..0.170 rows=43 loops=1)

  • Merge Cond: ((tst.administrationid = sset.administrationid) AND (tst.subtestid = sset.subtestid))
7. 0.011 0.016 ↑ 1.0 4 1

Sort (cost=1.08..1.09 rows=4 width=8) (actual time=0.014..0.016 rows=4 loops=1)

  • Sort Key: tst.administrationid, tst.subtestid
  • Sort Method: quicksort Memory: 25kB
8. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on dim_subtest tst (cost=0.00..1.04 rows=4 width=8) (actual time=0.004..0.005 rows=4 loops=1)

9. 0.046 0.057 ↓ 1.4 43 1

Sort (cost=3.04..3.11 rows=30 width=17) (actual time=0.023..0.057 rows=43 loops=1)

  • Sort Key: sset.administrationid, sset.subtestid
  • Sort Method: quicksort Memory: 28kB
10. 0.011 0.011 ↓ 1.4 43 1

Seq Scan on dim_scoreset sset (cost=0.00..2.30 rows=30 width=17) (actual time=0.001..0.011 rows=43 loops=1)

11. 262.516 770.689 ↓ 50,000.0 50,000 43

Materialize (cost=2,681.17..187,842.09 rows=1 width=482) (actual time=0.307..17.923 rows=50,000 loops=43)

12. 46.764 508.173 ↓ 50,000.0 50,000 1

Nested Loop (cost=2,681.17..187,842.09 rows=1 width=482) (actual time=13.087..508.173 rows=50,000 loops=1)

  • Join Filter: (x.administrationid = ses.administrationid)
13. 0.000 161.409 ↓ 50,000.0 50,000 1

Gather (cost=2,680.88..187,841.76 rows=1 width=478) (actual time=12.358..161.409 rows=50,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 29.193 353.321 ↓ 16,667.0 16,667 3 / 3

Nested Loop (cost=1,680.88..186,841.66 rows=1 width=478) (actual time=5.801..353.321 rows=16,667 loops=3)

15. 5.911 7.461 ↑ 1.2 16,667 3 / 3

Parallel Bitmap Heap Scan on stg_subtestobjective_score x (cost=1,680.45..131,121.30 rows=19,675 width=470) (actual time=1.730..7.461 rows=16,667 loops=3)

  • Recheck Cond: ((row_num >= 1) AND (row_num <= 50,000))
  • Heap Blocks: exact=223
16. 1.550 1.550 ↓ 1.1 50,000 1 / 3

Bitmap Index Scan on idx_stg_subtestobjective_score_1 (cost=0.00..1,668.64 rows=47,221 width=0) (actual time=4.651..4.651 rows=50,000 loops=1)

  • Index Cond: ((row_num >= 1) AND (row_num <= 50,000))
17. 316.667 316.667 ↑ 1.0 1 50,000 / 3

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

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

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

  • Index Cond: (assessmentsessionid = asmt.assessmentsessionid)
  • Filter: (asmt.administrationid = administrationid)
19. 153.418 153.418 ↑ 1.0 1 153,418

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=153,418)

  • Index Cond: (assessmentformid = asmt.assessmentformid)
  • Filter: (asmt.administrationid = administrationid)
20. 153.418 153.418 ↑ 1.0 1 153,418

Index Scan using pk_dim_assessmentlevel on dim_assessmentlevel lvl (cost=0.14..0.22 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=153,418)

  • Index Cond: (assessmentlevelid = frm.assessmentlevelid)
  • Filter: (frm.administrationid = administrationid)
21. 86.662 86.662 ↑ 1.0 1 43,331

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=43,331)

  • Index Cond: ((administrationid = ses.administrationid) AND (organizationid = ses.organizationid))
  • Filter: (orglevel = 2)
22. 259.986 259.986 ↑ 1.7 6 43,331

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.006 rows=6 loops=43,331)

  • Index Cond: ((administrationid = asmt.administrationid) AND (assessmentformid = asmt.assessmentformid))
  • Heap Fetches: 253,013
Planning time : 109.490 ms
Execution time : 2,104.698 ms