explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bqyE

Settings
# exclusive inclusive rows x rows loops node
1. 0.770 382,179.691 ↓ 5.0 5 1

Group (cost=84,329.66..84,329.69 rows=1 width=77) (actual time=382,178.779..382,179.691 rows=5 loops=1)

  • Group Key: dim_station.station_id, dim_airline.airline_id, dim_task_type.task_type_id, date.year4, date.year_month_number, dim_planning_state.planning_state_id, dim_airline.airline_name, dim_task_type.task_type_name, date.year_month_abbreviation
2. 4.305 382,178.921 ↓ 1,911.0 1,911 1

Sort (cost=84,329.66..84,329.67 rows=1 width=77) (actual time=382,178.776..382,178.921 rows=1,911 loops=1)

  • Sort Key: dim_station.station_id, dim_airline.airline_id, dim_task_type.task_type_id, date.year4, date.year_month_number, dim_planning_state.planning_state_id, dim_airline.airline_name, dim_task_type.task_type_name, date.year_month_abbreviation
  • Sort Method: quicksort Memory: 317kB
3. 190.146 382,174.616 ↓ 1,911.0 1,911 1

Nested Loop (cost=0.56..84,329.65 rows=1 width=77) (actual time=1.821..382,174.616 rows=1,911 loops=1)

  • Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
  • Rows Removed by Join Filter: 114037
4. 229,853.784 381,868.522 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..84,328.60 rows=1 width=78) (actual time=1.818..381,868.522 rows=115,948 loops=1)

  • Join Filter: (fact_task.snapshotstate_key = bridge_snapshot_state.snapshotstate_key)
  • Rows Removed by Join Filter: 2268290724
5. 1,773.581 4,644.830 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..83,762.41 rows=1 width=81) (actual time=0.844..4,644.830 rows=115,948 loops=1)

  • Join Filter: (fact_task.task_type_key = dim_task_type.task_type_key)
  • Rows Removed by Join Filter: 16812460
6. 139.474 1,479.873 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..83,753.13 rows=1 width=70) (actual time=0.825..1,479.873 rows=115,948 loops=1)

  • Join Filter: (fact_task.airline_key = dim_airline.airline_key)
  • Rows Removed by Join Filter: 1063615
7. 0.261 0.261 ↑ 1.0 1 1

Seq Scan on dim_airline (cost=0.00..44.74 rows=1 width=33) (actual time=0.258..0.261 rows=1 loops=1)

  • Filter: (airline_id = 'SXS'::text)
  • Rows Removed by Filter: 1978
8. 141.603 1,340.138 ↓ 654.9 1,179,563 1

Nested Loop (cost=0.56..83,685.88 rows=1,801 width=53) (actual time=0.368..1,340.138 rows=1,179,563 loops=1)

9. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on dim_station (cost=0.00..1.29 rows=1 width=8) (actual time=0.004..0.011 rows=1 loops=1)

  • Filter: (station_id = 'FRA'::text)
  • Rows Removed by Filter: 22
10. 151.869 1,198.524 ↓ 54.8 1,179,563 1

Nested Loop (cost=0.56..83,469.43 rows=21,516 width=53) (actual time=0.363..1,198.524 rows=1,179,563 loops=1)

11. 0.963 0.963 ↓ 10.3 31 1

Seq Scan on dim_date date (cost=0.00..156.90 rows=3 width=31) (actual time=0.333..0.963 rows=31 loops=1)

  • Filter: ((year_month_number = '2019-10'::text) AND (year4 = '2019'::text))
  • Rows Removed by Filter: 3629
12. 1,045.692 1,045.692 ↓ 5.3 38,050 31

Index Scan using fact_task_begin_date_key_idx on fact_task (cost=0.56..27,699.12 rows=7,172 width=40) (actual time=0.033..33.732 rows=38,050 loops=31)

  • Index Cond: ((begin_date_key = date.date_key) AND (hdp_section_id = dim_station.hdp_section_id))
13. 1,391.376 1,391.376 ↑ 1.0 146 115,948

Seq Scan on dim_task_type (cost=0.00..7.46 rows=146 width=27) (actual time=0.001..0.012 rows=146 loops=115,948)

14. 147,369.908 147,369.908 ↑ 1.0 19,564 115,948

Seq Scan on bridge_snapshot_state (cost=0.00..321.64 rows=19,564 width=19) (actual time=0.002..1.271 rows=19,564 loops=115,948)

15. 115.948 115.948 ↑ 1.0 1 115,948

Seq Scan on dim_planning_state (cost=0.00..1.04 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=115,948)

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
Planning time : 2.596 ms
Execution time : 382,179.792 ms