explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ECPS

Settings
# exclusive inclusive rows x rows loops node
1. 31.831 14,086.781 ↓ 181.0 181 1

HashAggregate (cost=463,305.13..463,305.14 rows=1 width=59) (actual time=14,086.740..14,086.781 rows=181 loops=1)

  • Group Key: dim_division.division_id, date.year4, date.year_month_number, date.year_month_abbreviation, date.date_short, date.date_key, dim_station.station_id
2. 99.279 14,054.950 ↓ 43,678.0 43,678 1

Nested Loop (cost=0.85..463,305.11 rows=1 width=59) (actual time=3.202..14,054.950 rows=43,678 loops=1)

  • Join Filter: (fact_task.division_key = dim_division.division_key)
  • Rows Removed by Join Filter: 786204
3. 472.531 13,868.315 ↓ 43,678.0 43,678 1

Nested Loop (cost=0.85..463,273.96 rows=1 width=54) (actual time=3.194..13,868.315 rows=43,678 loops=1)

  • Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
  • Rows Removed by Join Filter: 1390001
4. 1,090.191 13,395.784 ↓ 59,736.6 1,433,679 1

Nested Loop (cost=0.85..463,252.39 rows=24 width=62) (actual time=3.187..13,395.784 rows=1,433,679 loops=1)

5. 911.543 6,570.877 ↓ 59,736.6 1,433,679 1

Nested Loop (cost=0.56..463,159.97 rows=24 width=65) (actual time=3.172..6,570.877 rows=1,433,679 loops=1)

  • Join Filter: (fact_task.hdp_section_id = dim_station.hdp_section_id)
  • Rows Removed by Join Filter: 6213206
6. 0.011 0.011 ↑ 1.0 1 1

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

  • Filter: (station_id = 'HAM'::text)
  • Rows Removed by Filter: 19
7. 927.133 5,659.323 ↓ 215.5 7,646,885 1

Nested Loop (cost=0.56..462,687.98 rows=35,479 width=65) (actual time=0.370..5,659.323 rows=7,646,885 loops=1)

8. 0.980 0.980 ↓ 10.0 30 1

Seq Scan on dim_date date (cost=0.00..156.90 rows=3 width=42) (actual time=0.347..0.980 rows=30 loops=1)

  • Filter: ((year_month_number = '2019-09'::text) AND (year4 = '2019'::text))
  • Rows Removed by Filter: 3630
9. 4,731.210 4,731.210 ↓ 2.9 254,896 30

Index Scan using idx_fact_task_begin_date_key on fact_task (cost=0.56..153,288.23 rows=88,880 width=32) (actual time=0.019..157.707 rows=254,896 loops=30)

  • Index Cond: (begin_date_key = date.date_key)
10. 5,734.716 5,734.716 ↑ 1.0 1 1,433,679

Index Only Scan using bridge_snapshot_state_snapshotstate_key_idx on bridge_snapshot_state (cost=0.29..3.84 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=1,433,679)

  • Index Cond: (snapshotstate_key = fact_task.snapshotstate_key)
  • Heap Fetches: 1433679
11. 0.000 0.000 ↑ 4.0 1 1,433,679

Materialize (cost=0.00..20.14 rows=4 width=8) (actual time=0.000..0.000 rows=1 loops=1,433,679)

12. 0.003 0.003 ↑ 4.0 1 1

Seq Scan on dim_planning_state (cost=0.00..20.12 rows=4 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
13. 87.356 87.356 ↑ 49.5 19 43,678

Seq Scan on dim_division (cost=0.00..19.40 rows=940 width=21) (actual time=0.001..0.002 rows=19 loops=43,678)

Planning time : 1.558 ms
Execution time : 14,086.887 ms