explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IUY1

Settings
# exclusive inclusive rows x rows loops node
1. 0.434 984.535 ↓ 298.0 298 1

Nested Loop Left Join (cost=52,774.05..149,889.20 rows=1 width=97) (actual time=973.596..984.535 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.424 980.525 ↓ 298.0 298 1

Nested Loop (cost=52,773.63..149,886.03 rows=1 width=81) (actual time=973.560..980.525 rows=298 loops=1)

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

Nested Loop (cost=52,773.36..149,883.57 rows=1 width=90) (actual time=973.541..975.631 rows=298 loops=1)

4. 1.025 974.670 ↓ 298.0 298 1

Nested Loop (cost=52,773.08..149,879.27 rows=1 width=90) (actual time=973.519..974.670 rows=298 loops=1)

  • Join Filter: (asmt.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 3,278
5. 0.037 0.037 ↑ 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.020..0.037 rows=12 loops=1)

  • Filter: (administrationid = 593,317)
6. 0.384 973.608 ↓ 298.0 298 12

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

7. 0.024 973.224 ↓ 298.0 298 1

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

8. 13.169 972.306 ↓ 298.0 298 1

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 388.694 959.137 ↓ 99.0 99 3 / 3

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

  • Hash Cond: ((x.documentidcode = asmt.documentidcode) AND (x.testeventidcode = asmt.testeventidcode))
10. 421.384 421.384 ↑ 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.007..421.384 rows=1,374,108 loops=3)

  • Filter: (administrationid = 593,317)
11. 1.578 149.059 ↑ 2.0 1 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
12. 147.481 147.481 ↑ 2.0 1 3 / 3

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

  • Filter: ((administrationid = 593,317) AND (participantid = 13,843))
  • Rows Removed by Filter: 534,393
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 = 13,843)
  • Filter: (administrationid = 593,317)
14. 0.596 0.596 ↑ 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.002..0.002 rows=1 loops=298)

  • Index Cond: ((administrationid = 593,317) AND (orglevel = 2) AND (organizationid = pat.organizationid))
  • Heap Fetches: 0
15. 4.470 4.470 ↑ 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.015..0.015 rows=1 loops=298)

  • Index Cond: ((administrationid = 593,317) AND ((objectivecode)::text = (x.objectivecode)::text))
16. 3.576 3.576 ↑ 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.011..0.012 rows=1 loops=298)

  • Index Cond: ((x.administrationid = administrationid) AND (administrationid = 593,317) AND (organizationid = org.organizationid) AND (participantid = 13,843) AND ((itemidcode)::text = (x.itemidcode)::text) AND ((detailidcode)::text = (x.detailidcode)::text))
Planning time : 6.626 ms
Execution time : 984.901 ms