explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i0xX

Settings
# exclusive inclusive rows x rows loops node
1. 3,471.111 43,554.163 ↓ 2,550,906.0 2,550,906 1

Nested Loop Left Join (cost=66,959.76..210,182.24 rows=1 width=97) (actual time=6,244.918..43,554.163 rows=2,550,906 loops=1)

  • Join Filter: ((x.administrationid = fact_itm.administrationid) AND (fact_itm.subtestid = tst.subtestid) AND (fact_itm.objectiveid = obj.objectiveid) AND ((fact_itm.itemidcode)::text = (x.itemidcode)::text) AND ((fact_itm.detailidcode)::text = (x.detailidcode)::text))
  • Rows Removed by Join Filter: 11138684
2. 2,174.719 29,879.428 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,959.34..210,179.09 rows=1 width=81) (actual time=6,244.847..29,879.428 rows=2,550,906 loops=1)

3. 3,006.916 25,153.803 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,959.05..210,178.78 rows=1 width=81) (actual time=6,244.833..25,153.803 rows=2,550,906 loops=1)

4. 2,361.040 19,595.981 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,958.63..210,178.30 rows=1 width=73) (actual time=6,244.815..19,595.981 rows=2,550,906 loops=1)

  • Join Filter: (tst.subtestid = obj.subtestid)
5. 4,489.578 12,132.311 ↓ 212,609.6 2,551,315 1

Nested Loop (cost=66,958.36..210,174.64 rows=12 width=82) (actual time=6,244.766..12,132.311 rows=2,551,315 loops=1)

  • Join Filter: (asmt.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 28064465
6. 0.065 0.065 ↑ 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.018..0.065 rows=12 loops=1)

  • Filter: (administrationid = 593317)
7. 3,539.712 7,642.668 ↓ 212,609.6 2,551,315 12

Materialize (cost=66,958.22..210,160.16 rows=12 width=74) (actual time=250.780..636.889 rows=2,551,315 loops=12)

8. 126.844 4,102.956 ↓ 212,609.6 2,551,315 1

Gather (cost=66,958.22..210,160.10 rows=12 width=74) (actual time=3,009.318..4,102.956 rows=2,551,315 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 2,360.618 3,976.112 ↓ 170,087.6 850,438 3 / 3

Parallel Hash Join (cost=65,958.22..209,158.90 rows=5 width=74) (actual time=2,997.021..3,976.112 rows=850,438 loops=3)

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

  • Filter: (administrationid = 593317)
11. 310.813 589.616 ↑ 1.2 534,394 3 / 3

Parallel Hash (cost=50,106.74..50,106.74 rows=666,299 width=44) (actual time=589.616..589.616 rows=534,394 loops=3)

  • Buckets: 65536 Batches: 64 Memory Usage: 2528kB
12. 278.803 278.803 ↑ 1.2 534,394 3 / 3

Parallel Seq Scan on dim_assessmentparticipant asmt (cost=0.00..50,106.74 rows=666,299 width=44) (actual time=0.160..278.803 rows=534,394 loops=3)

  • Filter: (administrationid = 593317)
13. 5,102.630 5,102.630 ↑ 1.0 1 2,551,315

Index Scan using udx_dim_objective on dim_objective obj (cost=0.27..0.29 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=2,551,315)

  • Index Cond: ((administrationid = 593317) AND (subtestid = asmt.subtestid) AND ((objectivecode)::text = (x.objectivecode)::text))
14. 2,550.906 2,550.906 ↑ 1.0 1 2,550,906

Index Scan using pk_dim_participant on dim_participant pat (cost=0.42..0.48 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=2,550,906)

  • Index Cond: (participantid = asmt.participantid)
  • Filter: (administrationid = 593317)
15. 2,550.906 2,550.906 ↑ 1.0 1 2,550,906

Index Scan using pk_dim_organizations on dim_organizations org (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,550,906)

  • Index Cond: (organizationid = pat.organizationid)
  • Filter: ((administrationid = 593317) AND (orglevel = 2))
16. 10,203.624 10,203.624 ↓ 4.0 4 2,550,906

Index Scan using udx_fact_itemresult on fact_itemresult fact_itm (cost=0.42..3.13 rows=1 width=64) (actual time=0.002..0.004 rows=4 loops=2,550,906)

  • Index Cond: ((administrationid = 593317) AND (organizationid = org.organizationid) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
Planning time : 8.461 ms
Execution time : 43,804.650 ms