explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5291

Settings
# exclusive inclusive rows x rows loops node
1. 0.235 995.129 ↓ 298.0 298 1

Nested Loop Left Join (cost=52,774.05..149,885.17 rows=1 width=97) (actual time=988.978..995.129 rows=298 loops=1)

  • Join Filter: ((fact_itm.participantid = asmt.participantid) AND (fact_itm.assessmentparticipantid = asmt.assessmentparticipantid) AND (fact_itm.subtestid = tst.subtestid) AND (fact_itm.objectiveid = obj.objectiveid))
  • Rows Removed by Join Filter: 52
2. 0.336 992.510 ↓ 298.0 298 1

Nested Loop (cost=52,773.63..149,882.00 rows=1 width=81) (actual time=988.930..992.510 rows=298 loops=1)

  • Join Filter: (tst.subtestid = obj.subtestid)
  • Rows Removed by Join Filter: 92
3. 0.368 990.088 ↓ 298.0 298 1

Nested Loop (cost=52,773.36..149,879.54 rows=1 width=90) (actual time=988.906..990.088 rows=298 loops=1)

4. 0.496 989.422 ↓ 298.0 298 1

Nested Loop (cost=52,773.08..149,875.24 rows=1 width=90) (actual time=988.881..989.422 rows=298 loops=1)

  • Join Filter: (asmt.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 3278
5. 0.018 0.018 ↑ 1.0 12 1

Index Only Scan using pk_dim_subtest on dim_subtest tst (cost=0.14..8.31 rows=12 width=8) (actual time=0.014..0.018 rows=12 loops=1)

  • Heap Fetches: 0
6. 0.327 988.908 ↓ 298.0 298 12

Materialize (cost=52,772.94..149,866.74 rows=1 width=82) (actual time=29.964..82.409 rows=298 loops=12)

7. 0.032 988.581 ↓ 298.0 298 1

Nested Loop (cost=52,772.94..149,866.74 rows=1 width=82) (actual time=359.558..988.581 rows=298 loops=1)

8. 10.199 987.655 ↓ 298.0 298 1

Gather (cost=52,772.52..149,858.29 rows=1 width=74) (actual time=359.532..987.655 rows=298 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 433.797 977.456 ↓ 99.0 99 3 / 3

Parallel Hash Join (cost=51,772.52..148,858.19 rows=1 width=74) (actual time=249.137..977.456 rows=99 loops=3)

  • Hash Cond: ((x.documentidcode = asmt.documentidcode) AND (x.testeventidcode = asmt.testeventidcode))
10. 399.428 399.428 ↑ 1.2 1,374,108 3 / 3

Parallel Seq Scan on stg_item_score x (cost=0.00..84,203.41 rows=1,717,633 width=66) (actual time=0.009..399.428 rows=1,374,108 loops=3)

  • Filter: (administrationid = 593317)
11. 2.181 144.231 ↑ 2.0 1 3 / 3

Parallel Hash (cost=51,772.49..51,772.49 rows=2 width=44) (actual time=144.231..144.231 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
12. 142.050 142.050 ↑ 2.0 1 3 / 3

Parallel Seq Scan on dim_assessmentparticipant asmt (cost=0.00..51,772.49 rows=2 width=44) (actual time=93.630..142.050 rows=1 loops=3)

  • Filter: ((administrationid = 593317) AND (participantid = 13843))
  • Rows Removed by Filter: 534393
13. 0.894 0.894 ↑ 1.0 1 298

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

  • Index Cond: (participantid = 13843)
  • Filter: (administrationid = 593317)
14. 0.298 0.298 ↑ 1.0 1 298

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

  • Index Cond: ((administrationid = 593317) AND (orglevel = 2) AND (organizationid = pat.organizationid))
  • Heap Fetches: 0
15. 2.086 2.086 ↑ 1.0 1 298

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

  • Index Cond: ((administrationid = 593317) AND ((objectivecode)::text = (x.objectivecode)::text))
16. 2.384 2.384 ↑ 1.0 1 298

Index Scan using udx_fact_itemresult on fact_itemresult fact_itm (cost=0.42..3.15 rows=1 width=64) (actual time=0.007..0.008 rows=1 loops=298)

  • Index Cond: ((x.administrationid = administrationid) AND (administrationid = 593317) AND (organizationid = org.organizationid) AND (participantid = 13843) AND ((itemidcode)::text = (x.itemidcode)::text) AND ((detailidcode)::text = (x.detailidcode)::text))
Planning time : 5.900 ms
Execution time : 995.425 ms