explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pfoi

Settings
# exclusive inclusive rows x rows loops node
1. 25.620 1,358,972.311 ↑ 1.0 1 1

GroupAggregate (cost=20.74..1,240,159.73 rows=1 width=76) (actual time=1,358,972.311..1,358,972.311 rows=1 loops=1)

  • Group Key: dim_station.station_id, dim_task_assignment_state.assignment_state_id, dim_planning_state.planning_state_id
2. 683,620.791 1,358,946.691 ↓ 18,865.8 150,926 1

Nested Loop (cost=20.74..1,240,159.64 rows=8 width=76) (actual time=343,030.713..1,358,946.691 rows=150,926 loops=1)

  • Join Filter: (t.cost_key = c.cost_key)
  • Rows Removed by Join Filter: 6193852114
3. 94.780 94.780 ↑ 1.0 41,040 1

Seq Scan on fact_personell_cost_rate c (cost=0.00..672.40 rows=41,040 width=13) (actual time=0.018..94.780 rows=41,040 loops=1)

4. 672,695.368 675,231.120 ↓ 18,865.8 150,926 41,040

Materialize (cost=20.74..1,234,562.46 rows=8 width=88) (actual time=0.007..16.453 rows=150,926 loops=41,040)

5. 77.165 2,535.752 ↓ 18,865.8 150,926 1

Nested Loop (cost=20.74..1,234,562.42 rows=8 width=88) (actual time=90.749..2,535.752 rows=150,926 loops=1)

  • Join Filter: (t.assignment_state_key = dim_task_assignment_state.assignment_state_key)
  • Rows Removed by Join Filter: 38216
6. 267.524 2,458.587 ↓ 334.8 189,142 1

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

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

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

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

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

9. 2.042 4.321 ↑ 1.4 209 1

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

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

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

11. 0.002 0.012 ↑ 4.0 1 1

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

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

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

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
13. 1,298.517 1,298.517 ↑ 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.030..6.213 rows=13,130 loops=209)

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

Materialize (cost=0.00..18.64 rows=3 width=40) (actual time=0.000..0.000 rows=1 loops=189,142)

15. 0.009 0.009 ↑ 3.0 1 1

Seq Scan on dim_task_assignment_state (cost=0.00..18.62 rows=3 width=40) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (assignment_state_id = 'ASSIGNED'::text)
  • Rows Removed by Filter: 3
Planning time : 1.686 ms
Execution time : 1,358,974.526 ms