explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D63

Settings
# exclusive inclusive rows x rows loops node
1. 2.409 3,003.022 ↑ 1.0 1 1

GroupAggregate (cost=2.30..186,111.31 rows=1 width=64) (actual time=3,003.022..3,003.022 rows=1 loops=1)

  • Group Key: date.year4, date.year_month_number, date.date_short, dim_planning_state.planning_state_id
2. 17.281 3,000.613 ↓ 4,079.0 8,158 1

Nested Loop (cost=2.30..186,111.27 rows=2 width=64) (actual time=2,977.636..3,000.613 rows=8,158 loops=1)

  • Join Filter: (t.division_key = dim_division.division_key)
  • Rows Removed by Join Filter: 146844
3. 0.009 0.009 ↑ 1.0 19 1

Seq Scan on dim_division (cost=0.00..1.19 rows=19 width=8) (actual time=0.007..0.009 rows=19 loops=1)

4. 9.694 2,983.323 ↓ 4,079.0 8,158 19

Materialize (cost=2.30..186,109.52 rows=2 width=72) (actual time=4.823..157.017 rows=8,158 loops=19)

5. 106.018 2,973.629 ↓ 4,079.0 8,158 1

Nested Loop (cost=2.30..186,109.51 rows=2 width=72) (actual time=91.628..2,973.629 rows=8,158 loops=1)

  • Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
  • Rows Removed by Join Filter: 264990
6. 111.245 2,867.611 ↓ 2,276.2 273,148 1

Nested Loop (cost=2.30..186,082.17 rows=120 width=48) (actual time=0.912..2,867.611 rows=273,148 loops=1)

7. 615.600 1,390.626 ↓ 2,276.2 273,148 1

Nested Loop (cost=2.01..185,247.85 rows=120 width=51) (actual time=0.882..1,390.626 rows=273,148 loops=1)

  • Join Filter: (t.hdp_section_combination_key = dim_station.hdp_section_combination_key)
  • Rows Removed by Join Filter: 5189812
8. 82.358 501.878 ↓ 227.2 273,148 1

Hash Join (cost=2.01..184,886.00 rows=1,202 width=67) (actual time=0.869..501.878 rows=273,148 loops=1)

  • Hash Cond: (t.hdp_section_combination_key = s.hdp_section_combination_key)
9. 94.924 419.502 ↓ 22.7 273,148 1

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

10. 1.522 1.522 ↑ 1.0 1 1

Seq Scan on dim_date date (cost=0.00..166.05 rows=1 width=33) (actual time=0.758..1.522 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
11. 323.056 323.056 ↓ 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.071..323.056 rows=273,148 loops=1)

  • Index Cond: (begin_date_key = date.date_key)
12. 0.009 0.018 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.018..0.018 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.009 0.009 ↑ 1.0 20 1

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

14. 273.143 273.148 ↑ 1.0 20 273,148

Materialize (cost=0.00..1.30 rows=20 width=8) (actual time=0.000..0.001 rows=20 loops=273,148)

15. 0.005 0.005 ↑ 1.0 20 1

Seq Scan on dim_station (cost=0.00..1.20 rows=20 width=8) (actual time=0.002..0.005 rows=20 loops=1)

16. 1,365.740 1,365.740 ↑ 1.0 1 273,148

Index Scan using idx_bridge_snapshot_state_lookup on bridge_snapshot_state (cost=0.29..6.94 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=273,148)

  • Index Cond: (snapshotstate_key = t.snapshotstate_key)
17. 0.000 0.000 ↑ 4.0 1 273,148

Materialize (cost=0.00..20.14 rows=4 width=40) (actual time=0.000..0.000 rows=1 loops=273,148)

18. 0.004 0.004 ↑ 4.0 1 1

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

  • Filter: (planning_state_id = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
Planning time : 6.108 ms
Execution time : 3,003.278 ms