explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9ovE

Settings
# exclusive inclusive rows x rows loops node
1. 4,150.884 44,887.272 ↓ 2,550,906.0 2,550,906 1

Nested Loop Left Join (cost=66,959.76..210,178.21 rows=1 width=97) (actual time=6,248.890..44,887.272 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: 11,138,684
2. 2,647.089 30,532.764 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,959.34..210,175.05 rows=1 width=81) (actual time=6,248.818..30,532.764 rows=2,550,906 loops=1)

3. 2,973.910 25,334.769 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,959.05..210,174.74 rows=1 width=81) (actual time=6,248.802..25,334.769 rows=2,550,906 loops=1)

4. 2,512.061 19,809.953 ↓ 2,550,906.0 2,550,906 1

Nested Loop (cost=66,958.63..210,174.27 rows=1 width=73) (actual time=6,248.786..19,809.953 rows=2,550,906 loops=1)

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

Nested Loop (cost=66,958.36..210,170.61 rows=12 width=82) (actual time=6,248.733..12,195.262 rows=2,551,315 loops=1)

  • Join Filter: (asmt.subtestid = tst.subtestid)
  • Rows Removed by Join Filter: 28,064,465
6. 0.042 0.042 ↑ 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.015..0.042 rows=12 loops=1)

  • Heap Fetches: 0
7. 3,562.608 7,747.728 ↓ 212,609.6 2,551,315 12

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

8. 142.319 4,185.120 ↓ 212,609.6 2,551,315 1

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 2,445.569 4,042.801 ↓ 170,087.6 850,438 3 / 3

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

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

  • Filter: (administrationid = 593,317)
11. 305.165 587.301 ↑ 1.2 534,394 3 / 3

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,528kB
12. 282.136 282.136 ↑ 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.199..282.136 rows=534,394 loops=3)

  • Filter: (administrationid = 593,317)
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 = 593,317) 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.47 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2,550,906)

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

Index Only Scan using idx_dim_organizations_1 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: ((administrationid = 593,317) AND (orglevel = 2) AND (organizationid = pat.organizationid))
  • Heap Fetches: 0
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 = 593,317) AND (organizationid = org.organizationid) AND (participantid = asmt.participantid) AND (assessmentparticipantid = asmt.assessmentparticipantid))
Planning time : 5.462 ms
Execution time : 45,120.870 ms