explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YT0

Settings
# exclusive inclusive rows x rows loops node
1. 4.086 374,593.005 ↓ 5.0 5 1

HashAggregate (cost=61,382.43..61,382.44 rows=1 width=36) (actual time=374,593.004..374,593.005 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.159 374,588.919 ↓ 1,911.0 1,911 1

Nested Loop (cost=0.56..61,382.41 rows=1 width=36) (actual time=17,113.136..374,588.919 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. 231,091.567 374,293.812 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..61,381.36 rows=1 width=37) (actual time=0.914..374,293.812 rows=115,948 loops=1)

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

Nested Loop (cost=0.56..60,815.17 rows=1 width=40) (actual time=0.858..5,340.073 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. 133.264 1,598.110 ↓ 115,948.0 115,948 1

Nested Loop (cost=0.56..60,806.74 rows=1 width=44) (actual time=0.847..1,598.110 rows=115,948 loops=1)

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

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

  • Filter: (airline_id = 'SXS'::text)
  • Rows Removed by Filter: 1978
7. 139.063 1,464.522 ↓ 981.3 1,179,563 1

Nested Loop (cost=0.56..60,746.97 rows=1,202 width=48) (actual time=0.407..1,464.522 rows=1,179,563 loops=1)

8. 0.019 0.019 ↑ 1.0 1 1

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

  • Filter: (station_id = 'FRA'::text)
  • Rows Removed by Filter: 22
9. 153.896 1,325.440 ↓ 76.5 1,179,563 1

Nested Loop (cost=0.56..60,591.49 rows=15,420 width=48) (actual time=0.389..1,325.440 rows=1,179,563 loops=1)

10. 1.015 1.015 ↓ 10.3 31 1

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

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

Index Scan using fact_task_begin_date_key_idx on fact_task (cost=0.56..20,093.46 rows=5,140 width=44) (actual time=0.027..37.759 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,862.172 137,862.172 ↑ 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.189 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 : 4.210 ms
Execution time : 374,593.194 ms