explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uca0

Settings
# exclusive inclusive rows x rows loops node
1. 62.858 3,639.205 ↑ 66.0 3 1

HashAggregate (cost=1,243,859.10..1,243,861.58 rows=198 width=76) (actual time=3,639.203..3,639.205 rows=3 loops=1)

  • Group Key: dim_station.station_id, dim_task_assignment_state.assignment_state_id, dim_planning_state.planning_state_id
2. 93.381 3,576.347 ↓ 97.0 189,142 1

Nested Loop (cost=21.03..1,243,839.61 rows=1,949 width=76) (actual time=3,440.583..3,576.347 rows=189,142 loops=1)

  • Join Filter: (t.assignment_state_key = dim_task_assignment_state.assignment_state_key)
  • Rows Removed by Join Filter: 567426
3. 0.014 0.014 ↑ 172.5 4 1

Seq Scan on dim_task_assignment_state (cost=0.00..16.90 rows=690 width=40) (actual time=0.004..0.014 rows=4 loops=1)

4. 122.686 3,482.952 ↓ 334.8 189,142 4

Materialize (cost=21.03..1,237,976.38 rows=565 width=52) (actual time=27.457..870.738 rows=189,142 loops=4)

5. 25.456 3,360.266 ↓ 334.8 189,142 1

Nested Loop (cost=21.03..1,237,973.55 rows=565 width=52) (actual time=109.767..3,360.266 rows=189,142 loops=1)

6. 253.661 2,389.100 ↓ 334.8 189,142 1

Nested Loop (cost=20.74..1,234,518.36 rows=565 width=64) (actual time=109.738..2,389.100 rows=189,142 loops=1)

  • Join Filter: (t.station_key = dim_station.station_key)
  • Rows Removed by Join Filter: 2555100
7. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on dim_station (cost=0.00..27.25 rows=1 width=12) (actual time=0.005..0.008 rows=1 loops=1)

  • Filter: (station_id = 'HAJ'::text)
  • Rows Removed by Filter: 18
8. 832.009 2,135.431 ↓ 3.5 2,744,242 1

Nested Loop (cost=20.74..1,224,743.81 rows=779,784 width=68) (actual time=0.293..2,135.431 rows=2,744,242 loops=1)

9. 1.852 4.069 ↑ 1.4 209 1

Hash Join (cost=20.18..373.81 rows=283 width=43) (actual time=0.237..4.069 rows=209 loops=1)

  • Hash Cond: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
10. 2.202 2.202 ↑ 1.0 14,168 1

Seq Scan on bridge_snapshot_state (cost=0.00..297.68 rows=14,168 width=19) (actual time=0.014..2.202 rows=14,168 loops=1)

11. 0.002 0.015 ↑ 4.0 1 1

Hash (cost=20.12..20.12 rows=4 width=40) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.013 0.013 ↑ 4.0 1 1

Seq Scan on dim_planning_state (cost=0.00..20.12 rows=4 width=40) (actual time=0.012..0.013 rows=1 loops=1)

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
13. 1,299.353 1,299.353 ↑ 1.7 13,130 209

Index Scan using idx_fact_task_snapshotstate_key on fact_task t (cost=0.56..4,107.36 rows=21,904 width=47) (actual time=0.028..6.217 rows=13,130 loops=209)

  • Index Cond: (snapshotstate_key = bridge_snapshot_state.snapshotstate_key)
14. 945.710 945.710 ↑ 1.0 1 189,142

Index Only Scan using idx_fact_personell_cost_rate_cost_key on fact_personell_cost_rate c (cost=0.29..6.11 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=189,142)

  • Index Cond: (cost_key = t.cost_key)
  • Heap Fetches: 189142
Planning time : 1.913 ms
Execution time : 3,641.091 ms