explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4NRh

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 380.428 ↓ 20.0 20 1

Nested Loop Left Join (cost=1,009.99..39,633.82 rows=1 width=244) (actual time=0.864..380.428 rows=20 loops=1)

  • Join Filter: ((x.administrationid = fact_itm.administrationid) AND (fact_itm.subtestid = meta.subtestid) AND (fact_itm.assessmentlevelid = meta.assessmentlevelid) AND (fact_itm.assessmentformid = meta.assessmentformid) AND (fact_itm.objectiveid = meta.objectiveid) AND (fact_itm.assessmentitemid = meta.assessmentitemid))
  • Rows Removed by Join Filter: 190
2. 0.048 380.140 ↓ 20.0 20 1

Nested Loop (cost=1,009.44..39,625.22 rows=1 width=228) (actual time=0.837..380.140 rows=20 loops=1)

3. 0.012 379.812 ↓ 20.0 20 1

Nested Loop (cost=1,009.03..39,624.76 rows=1 width=230) (actual time=0.801..379.812 rows=20 loops=1)

4. 0.000 379.740 ↓ 20.0 20 1

Nested Loop (cost=1,008.75..39,624.03 rows=1 width=230) (actual time=0.791..379.740 rows=20 loops=1)

5. 10.913 379.734 ↓ 20.0 20 1

Gather (cost=1,008.46..39,615.73 rows=1 width=230) (actual time=0.782..379.734 rows=20 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 157.365 368.821 ↓ 7.0 7 3

Hash Join (cost=8.46..38,615.63 rows=1 width=230) (actual time=242.537..368.821 rows=7 loops=3)

  • Hash Cond: ((x.documentidcode = asmt.documentidcode) AND (x.testeventidcode = asmt.testeventidcode))
7. 211.429 211.429 ↑ 1.2 510,233 3

Parallel Seq Scan on stg_item_score x (cost=0.00..35,271.07 rows=635,446 width=222) (actual time=1.544..211.429 rows=510,233 loops=3)

  • Filter: (administrationid = 593218)
8. 0.006 0.027 ↑ 1.0 1 3

Hash (cost=8.45..8.45 rows=1 width=40) (actual time=0.027..0.027 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.021 0.021 ↑ 1.0 1 3

Index Scan using pk_dim_assessmentparticipant on dim_assessmentparticipant asmt (cost=0.43..8.45 rows=1 width=40) (actual time=0.020..0.021 rows=1 loops=3)

  • Index Cond: (assessmentparticipantid = 103730)
  • Filter: (administrationid = 593218)
10. 0.060 0.060 ↑ 1.0 1 20

Index Scan using pk_dim_assessmentsession on dim_assessmentsession ses (cost=0.28..8.30 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: (assessmentsessionid = asmt.assessmentsessionid)
  • Filter: (administrationid = 593218)
11. 0.060 0.060 ↑ 1.0 1 20

Index Scan using pk_dim_organizations on dim_organizations org (cost=0.29..0.73 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: (organizationid = ses.organizationid)
  • Filter: ((administrationid = 593218) AND (orglevel = 2))
12. 0.280 0.280 ↑ 1.0 1 20

Index Scan using idx_mv_metadata_1 on mv_metadata meta (cost=0.41..0.45 rows=1 width=88) (actual time=0.014..0.014 rows=1 loops=20)

  • Index Cond: ((administrationid = 593218) AND ((subtestcode)::text = (x.subtestcode)::text) AND ((levelcode)::text = (x.levelcode)::text) AND ((formcode)::text = (x.formcode)::text) AND ((objectivecode)::text = (x.objectivecode)::text) AND ((itemidcode)::text = (x.itemidcode)::text) AND ((detailidcode)::text = (x.detailidcode)::text))
13. 0.200 0.200 ↓ 10.0 10 20

Index Scan using udx_fact_itemresult on fact_itemresult fact_itm (cost=0.55..8.58 rows=1 width=76) (actual time=0.007..0.010 rows=10 loops=20)

  • Index Cond: ((administrationid = 593218) AND (organizationid = org.organizationid) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid) AND (assessmentparticipantid = 103730))