explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v3Fc

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 158.508 ↓ 29.0 29 1

Nested Loop Left Join (cost=1,002.09..52,825.71 rows=1 width=82) (actual time=157.914..158.508 rows=29 loops=1)

  • Join Filter: ((x.administrationid = fact.administrationid) AND (fact.participantid = asmt.participantid) AND (fact.assessmentparticipantid = asmt.assessmentparticipantid) AND (fact.subtestid = obj.subtestid) AND (fact.objectiveid = obj.objectiveid))
  • Rows Removed by Join Filter: 406
2. 0.041 158.183 ↓ 29.0 29 1

Nested Loop (cost=1,001.53..52,817.24 rows=1 width=66) (actual time=157.880..158.183 rows=29 loops=1)

  • Join Filter: (tst.subtestid = obj.subtestid)
  • Rows Removed by Join Filter: 7
3. 0.059 157.910 ↓ 29.0 29 1

Nested Loop (cost=1,001.26..52,814.79 rows=1 width=77) (actual time=157.855..157.910 rows=29 loops=1)

  • Join Filter: (asmt.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 319
4. 0.027 0.027 ↑ 1.0 12 1

Index Scan using pk_dim_subtest on dim_subtest tst (cost=0.14..12.35 rows=12 width=12) (actual time=0.021..0.027 rows=12 loops=1)

  • Filter: (administrationid = 593317)
5. 0.035 157.824 ↓ 29.0 29 12

Materialize (cost=1,001.13..52,802.27 rows=1 width=69) (actual time=0.351..13.152 rows=29 loops=12)

6. 0.040 157.789 ↓ 29.0 29 1

Nested Loop (cost=1,001.13..52,802.26 rows=1 width=69) (actual time=4.206..157.789 rows=29 loops=1)

7. 0.000 157.662 ↓ 29.0 29 1

Nested Loop (cost=1,000.85..52,797.96 rows=1 width=69) (actual time=4.185..157.662 rows=29 loops=1)

8. 10.382 157.565 ↓ 29.0 29 1

Gather (cost=1,000.42..52,789.50 rows=1 width=61) (actual time=4.166..157.565 rows=29 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 0.011 147.183 ↓ 10.0 10 3 / 3

Nested Loop (cost=0.42..51,789.40 rows=1 width=61) (actual time=96.070..147.183 rows=10 loops=3)

10. 147.148 147.148 ↑ 2.0 1 3 / 3

Parallel Seq Scan on dim_assessmentparticipant asmt (cost=0.00..51,772.49 rows=2 width=52) (actual time=96.060..147.148 rows=1 loops=3)

  • Filter: ((administrationid = 593317) AND (participantid = 13843))
  • Rows Removed by Filter: 534393
11. 0.024 0.024 ↓ 7.0 7 4 / 3

Index Scan using pk_stg_subtestobjective_score on stg_subtestobjective_score x (cost=0.42..8.45 rows=1 width=61) (actual time=0.014..0.018 rows=7 loops=4)

  • Index Cond: ((administrationid = 593317) AND (studentidcode = asmt.studentidcode) AND (documentidcode = asmt.documentidcode) AND (testeventidcode = asmt.testeventidcode))
12. 0.116 0.116 ↑ 1.0 1 29

Index Scan using pk_dim_participant on dim_participant pat (cost=0.42..8.44 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=29)

  • Index Cond: (participantid = 13843)
  • Filter: (administrationid = 593317)
13. 0.087 0.087 ↑ 1.0 1 29

Index Only Scan using idx_dim_organizations_1 on dim_organizations org (cost=0.29..4.31 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=29)

  • Index Cond: ((administrationid = 593317) AND (orglevel = 2) AND (organizationid = pat.organizationid))
  • Heap Fetches: 0
14. 0.232 0.232 ↑ 1.0 1 29

Index Scan using udx_dim_objective on dim_objective obj (cost=0.27..2.44 rows=1 width=31) (actual time=0.008..0.008 rows=1 loops=29)

  • Index Cond: ((administrationid = 593317) AND ((objectivecode)::text = (x.objectivecode)::text))
15. 0.232 0.232 ↓ 15.0 15 29

Index Scan using udx_fact_subtestobjectiveresult on fact_subtestobjectiveresult fact (cost=0.56..8.44 rows=1 width=52) (actual time=0.004..0.008 rows=15 loops=29)

  • Index Cond: ((administrationid = 593317) AND (organizationid = org.organizationid) AND (participantid = 13843))
Planning time : 5.195 ms
Execution time : 158.664 ms