explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wTF3

Settings
# exclusive inclusive rows x rows loops node
1. 3.947 378,547.788 ↓ 5.0 5 1

HashAggregate (cost=58,258.95..58,258.96 rows=1 width=36) (actual time=378,547.786..378,547.788 rows=5 loops=1)

  • Group Key: date.year4, date.year_month_number, dim_airline.airline_id, dim_task_type.task_type_id, dim_station.station_id, dim_planning_state.planning_state_id
2. 179.158 378,543.841 ↓ 1,911.0 1,911 1

Nested Loop (cost=0.56..58,258.93 rows=1 width=36) (actual time=16,981.113..378,543.841 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
3. 229,781.394 378,248.735 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..58,257.88 rows=1 width=37) (actual time=0.868..378,248.735 rows=115,948 loops=1)

  • Join Filter: (fact_task.snapshotstate_key = bridge_snapshot_state.snapshotstate_key)
  • Rows Removed by Join Filter: 2268290724
4. 229.012 11,184.909 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..57,691.69 rows=1 width=40) (actual time=0.803..11,184.909 rows=115,948 loops=1)

  • Join Filter: (fact_task.task_type_key = dim_task_type.task_type_key)
  • Rows Removed by Join Filter: 463792
5. 134.548 7,477.457 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..57,683.26 rows=1 width=44) (actual time=0.792..7,477.457 rows=115,948 loops=1)

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

Seq Scan on dim_airline (cost=0.00..44.74 rows=1 width=12) (actual time=0.266..0.269 rows=1 loops=1)

  • Filter: (airline_id = 'SXS'::text)
  • Rows Removed by Filter: 1978
7. 139.618 7,342.640 ↓ 962.1 1,179,563 1

Nested Loop (cost=0.56..57,623.19 rows=1,226 width=48) (actual time=0.411..7,342.640 rows=1,179,563 loops=1)

8. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (station_id = 'FRA'::text)
  • Rows Removed by Filter: 22
9. 155.539 7,203.016 ↓ 80.8 1,179,563 1

Nested Loop (cost=0.56..57,475.96 rows=14,595 width=48) (actual time=0.406..7,203.016 rows=1,179,563 loops=1)

10. 1.084 1.084 ↓ 10.3 31 1

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

  • Filter: ((year4 = '2019'::text) AND (year_month_number = '2019-10'::text))
  • Rows Removed by Filter: 3629
11. 7,046.393 7,046.393 ↓ 7.8 38,050 31

Index Scan using fact_task_begin_date_key_idx on fact_task (cost=0.56..19,057.70 rows=4,865 width=44) (actual time=0.324..227.303 rows=38,050 loops=31)

  • Index Cond: ((begin_date_key = date.date_key) AND (hdp_section_id = dim_station.hdp_section_id) AND (hdp_section_id = ANY ('{AVI,BER,BEX,BRE,BRX,CGX,DRS,DUS,EGR,FRA,HAJ,HAM,HQCGX,HQDUS,HQHAJ,HQHAM,HQMUC,LEJ,MUC,SGS,TST,TXL,TXX}'::text[])))
12. 3,478.440 3,478.440 ↑ 1.0 5 115,948

Seq Scan on dim_task_type (cost=0.00..8.37 rows=5 width=12) (actual time=0.007..0.030 rows=5 loops=115,948)

  • Filter: (task_type_id = ANY ('{B,CI,LL,R,W&B}'::text[]))
  • Rows Removed by Filter: 141
13. 137,282.432 137,282.432 ↑ 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.184 rows=19,564 loops=115,948)

14. 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.920 ms
Execution time : 378,547.898 ms