explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bw11

Settings
# exclusive inclusive rows x rows loops node
1. 2.093 253,495.304 ↑ 1.0 1 1

GroupAggregate (cost=0.56..185,498.08 rows=1 width=64) (actual time=253,495.304..253,495.304 rows=1 loops=1)

  • Group Key: date.year4, date.year_month_number, date.date_short, dim_planning_state.planning_state_id
2. 119.791 253,493.211 ↓ 1,656.0 1,656 1

Nested Loop (cost=0.56..185,498.05 rows=1 width=64) (actual time=8,063.974..253,493.211 rows=1,656 loops=1)

  • Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
  • Rows Removed by Join Filter: 52711
3. 144,434.002 253,210.319 ↓ 54,367.0 54,367 1

Nested Loop (cost=0.56..185,477.88 rows=1 width=40) (actual time=730.334..253,210.319 rows=54,367 loops=1)

  • Join Filter: (t.snapshotstate_key = bridge_snapshot_state.snapshotstate_key)
  • Rows Removed by Join Filter: 911571489
4. 181.678 1,020.923 ↓ 54,367.0 54,367 1

Nested Loop (cost=0.56..184,989.07 rows=1 width=43) (actual time=727.466..1,020.923 rows=54,367 loops=1)

  • Join Filter: (t.hdp_section_combination_key = s.hdp_section_combination_key)
  • Rows Removed by Join Filter: 1032973
5. 0.025 0.025 ↑ 1.0 20 1

Seq Scan on dim_station s (cost=0.00..1.20 rows=20 width=8) (actual time=0.004..0.025 rows=20 loops=1)

6. 212.666 839.220 ↓ 4,182.1 54,367 20

Materialize (cost=0.56..184,984.00 rows=13 width=59) (actual time=0.905..41.961 rows=54,367 loops=20)

7. 55.974 626.554 ↓ 4,182.1 54,367 1

Nested Loop (cost=0.56..184,983.93 rows=13 width=59) (actual time=18.002..626.554 rows=54,367 loops=1)

  • Join Filter: (t.division_key = dim_division.division_key)
  • Rows Removed by Join Filter: 173577
8. 58.301 570.580 ↓ 949.8 56,986 1

Nested Loop (cost=0.56..184,978.94 rows=60 width=67) (actual time=17.989..570.580 rows=56,986 loops=1)

  • Join Filter: (t.hdp_section_combination_key = dim_station.hdp_section_combination_key)
  • Rows Removed by Join Filter: 216162
9. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on dim_station (cost=0.00..1.25 rows=1 width=8) (actual time=0.003..0.006 rows=1 loops=1)

  • Filter: (station_id = 'DUS'::text)
  • Rows Removed by Filter: 19
10. 97.030 512.273 ↓ 22.7 273,148 1

Nested Loop (cost=0.56..184,827.46 rows=12,019 width=59) (actual time=0.478..512.273 rows=273,148 loops=1)

11. 1.177 1.177 ↑ 1.0 1 1

Seq Scan on dim_date date (cost=0.00..166.05 rows=1 width=33) (actual time=0.434..1.177 rows=1 loops=1)

  • Filter: ((year4 = '2019'::text) AND (year_month_number = '2019-09'::text) AND (date_short = '04/09/2019'::text))
  • Rows Removed by Filter: 3659
12. 414.066 414.066 ↓ 3.0 273,148 1

Index Scan using idx_fact_task_begin_date_key on fact_task t (cost=0.56..183,759.97 rows=90,144 width=44) (actual time=0.041..414.066 rows=273,148 loops=1)

  • Index Cond: (begin_date_key = date.date_key)
13. 0.000 0.000 ↑ 1.0 4 56,986

Materialize (cost=0.00..1.40 rows=4 width=8) (actual time=0.000..0.000 rows=4 loops=56,986)

14. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on dim_division (cost=0.00..1.38 rows=4 width=8) (actual time=0.007..0.010 rows=4 loops=1)

  • Filter: ((division_id = 'LOST & FOUND'::text) OR (division_id = 'OPERATIONS'::text) OR (division_id = 'PASSAGE'::text) OR (division_id = 'TICKETING'::text))
  • Rows Removed by Filter: 15
15. 107,755.394 107,755.394 ↑ 1.0 16,768 54,367

Seq Scan on bridge_snapshot_state (cost=0.00..277.25 rows=16,925 width=19) (actual time=0.003..1.982 rows=16,768 loops=54,367)

16. 163.101 163.101 ↑ 4.0 1 54,367

Seq Scan on dim_planning_state (cost=0.00..20.12 rows=4 width=40) (actual time=0.002..0.003 rows=1 loops=54,367)

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
Planning time : 3.190 ms
Execution time : 253,496.160 ms