explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sgkr

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 6.690 ↑ 1.0 1 1

GroupAggregate (cost=922.58..1,460.43 rows=1 width=380) (actual time=6.690..6.690 rows=1 loops=1)

  • Group Key: capacity_sub.stationid, capacity_sub.daypart
  • Buffers: shared hit=1,181 read=1
2.          

CTE capacity_sub

3. 0.015 0.097 ↑ 1.0 4 1

GroupAggregate (cost=15.15..15.28 rows=4 width=44) (actual time=0.091..0.097 rows=4 loops=1)

  • Group Key: capacity_wo_non_distributed.stationid, capacity_wo_non_distributed.aired_date, capacity_wo_non_distributed.aired_dow, capacity_wo_non_distributed.daypart_placed, (CASE WHEN ((capacity_wo_non_distributed.break_code)::text = ANY ('{"ADJ TWIN",TWIN}'::text[])) THEN true ELSE false END)
  • Buffers: shared hit=17
4. 0.043 0.082 ↑ 1.0 4 1

Sort (cost=15.15..15.16 rows=4 width=18) (actual time=0.081..0.082 rows=4 loops=1)

  • Sort Key: capacity_wo_non_distributed.aired_date, (CASE WHEN ((capacity_wo_non_distributed.break_code)::text = ANY ('{"ADJ TWIN",TWIN}'::text[])) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=17
5. 0.002 0.039 ↑ 1.0 4 1

Result (cost=0.00..15.11 rows=4 width=18) (actual time=0.030..0.039 rows=4 loops=1)

  • Buffers: shared hit=9
6. 0.001 0.037 ↑ 1.0 4 1

Append (cost=0.00..15.06 rows=4 width=20) (actual time=0.029..0.037 rows=4 loops=1)

  • Buffers: shared hit=9
7. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on capacity_wo_non_distributed (cost=0.00..0.00 rows=1 width=19) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND (aired_dow = 6) AND ((daypart_placed)::text = 'MD'::text) AND (stationid = '187'::smallint))
8. 0.033 0.033 ↓ 1.3 4 1

Index Scan using capacity_wo_non_distributed__aired_date_aired_dow_daypar_idx107 on capacity_wo_non_distributed_187 (cost=0.29..15.06 rows=3 width=20) (actual time=0.026..0.033 rows=4 loops=1)

  • Index Cond: ((aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND (aired_dow = 6) AND ((daypart_placed)::text = 'MD'::text))
  • Filter: (stationid = '187'::smallint)
  • Buffers: shared hit=9
9.          

CTE rate_cap

10. 0.012 0.012 ↑ 1.0 1 1

Index Scan using unique_station_params on stationparameters (cost=0.28..8.30 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 13))
  • Buffers: shared hit=3
11.          

CTE daypart_multi

12. 0.018 0.109 ↑ 13.2 4 1

HashAggregate (cost=42.62..43.41 rows=53 width=43) (actual time=0.107..0.109 rows=4 loops=1)

  • Group Key: daypartparameters.stationid, daypartparameters.daypart, daypartparameters.is_twin, daypartparameters.wd_we
  • Buffers: shared hit=14
13. 0.004 0.091 ↑ 1.4 38 1

Append (cost=0.42..41.95 rows=53 width=11) (actual time=0.033..0.091 rows=38 loops=1)

  • Buffers: shared hit=14
14. 0.052 0.052 ↑ 1.3 38 1

Index Only Scan using unique_daypart_params on daypartparameters (cost=0.42..16.99 rows=50 width=11) (actual time=0.033..0.052 rows=38 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=8
15. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using unique_derived_diffs on derived_daypart_rate_diffs (cost=0.28..8.32 rows=1 width=13) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=2
16. 0.013 0.013 ↓ 0.0 0 1

Index Only Scan using unique_derived_diffs on derived_daypart_rate_diffs derived_daypart_rate_diffs_1 (cost=0.28..8.32 rows=1 width=13) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=2
17. 0.010 0.010 ↓ 0.0 0 1

Index Only Scan using unique_derived_diffs on derived_daypart_rate_diffs derived_daypart_rate_diffs_2 (cost=0.28..8.32 rows=1 width=13) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=2
18.          

CTE rate_multi

19. 0.033 0.206 ↑ 3.2 4 1

HashAggregate (cost=6.09..6.51 rows=13 width=159) (actual time=0.203..0.206 rows=4 loops=1)

  • Group Key: stationparameters_1.stationid, daypart_multi.daypart, daypart_multi.wd_we, daypart_multi.is_twin, daypart_multi.param_value
  • Buffers: shared hit=54
20. 0.012 0.173 ↓ 4.3 56 1

Nested Loop (cost=0.28..5.83 rows=13 width=127) (actual time=0.135..0.173 rows=56 loops=1)

  • Buffers: shared hit=54
21. 0.113 0.113 ↓ 4.0 4 1

CTE Scan on daypart_multi (cost=0.00..1.19 rows=1 width=127) (actual time=0.110..0.113 rows=4 loops=1)

  • Filter: (stationid = 187)
  • Buffers: shared hit=14
22. 0.048 0.048 ↓ 1.1 14 4

Index Only Scan using unique_station_params on stationparameters stationparameters_1 (cost=0.28..4.51 rows=13 width=4) (actual time=0.009..0.012 rows=14 loops=4)

  • Index Cond: (stationid = 187)
  • Heap Fetches: 32
  • Buffers: shared hit=40
23.          

CTE thirty_rate

24. 0.004 0.051 ↑ 1.3 38 1

Append (cost=0.42..137.73 rows=48 width=15) (actual time=0.023..0.051 rows=38 loops=1)

  • Buffers: shared hit=39
25. 0.041 0.041 ↑ 1.3 34 1

Index Scan using unique_daypart_params on daypartparameters daypartparameters_1 (cost=0.42..119.28 rows=43 width=15) (actual time=0.023..0.041 rows=34 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 1))
  • Buffers: shared hit=36
26. 0.001 0.006 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_3 (cost=4.32..18.45 rows=5 width=17) (actual time=0.005..0.006 rows=4 loops=1)

  • Recheck Cond: (stationid = 187)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
27. 0.005 0.005 ↑ 1.2 4 1

Bitmap Index Scan on unique_derived_diffs (cost=0.00..4.31 rows=5 width=0) (actual time=0.004..0.005 rows=4 loops=1)

  • Index Cond: (stationid = 187)
  • Buffers: shared hit=2
28.          

CTE fifteen_rate

29. 0.004 0.042 ↑ 1.3 38 1

Append (cost=0.42..137.73 rows=48 width=15) (actual time=0.015..0.042 rows=38 loops=1)

  • Buffers: shared hit=37
30. 0.032 0.032 ↑ 1.3 34 1

Index Scan using unique_daypart_params on daypartparameters daypartparameters_2 (cost=0.42..119.28 rows=43 width=15) (actual time=0.015..0.032 rows=34 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 2))
  • Buffers: shared hit=34
31. 0.001 0.006 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_4 (cost=4.32..18.45 rows=5 width=17) (actual time=0.006..0.006 rows=4 loops=1)

  • Recheck Cond: (stationid = 187)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
32. 0.005 0.005 ↑ 1.2 4 1

Bitmap Index Scan on unique_derived_diffs (cost=0.00..4.31 rows=5 width=0) (actual time=0.005..0.005 rows=4 loops=1)

  • Index Cond: (stationid = 187)
  • Buffers: shared hit=2
33.          

CTE ten_rate

34. 0.004 0.045 ↑ 1.3 38 1

Append (cost=0.42..137.73 rows=48 width=15) (actual time=0.009..0.045 rows=38 loops=1)

  • Buffers: shared hit=35
35. 0.030 0.030 ↑ 1.3 34 1

Index Scan using unique_daypart_params on daypartparameters daypartparameters_3 (cost=0.42..119.28 rows=43 width=15) (actual time=0.008..0.030 rows=34 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 3))
  • Buffers: shared hit=32
36. 0.004 0.011 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_5 (cost=4.32..18.45 rows=5 width=17) (actual time=0.010..0.011 rows=4 loops=1)

  • Recheck Cond: (stationid = 187)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
37. 0.007 0.007 ↑ 1.2 4 1

Bitmap Index Scan on unique_derived_diffs (cost=0.00..4.31 rows=5 width=0) (actual time=0.007..0.007 rows=4 loops=1)

  • Index Cond: (stationid = 187)
  • Buffers: shared hit=2
38.          

CTE hot_program

39. 0.005 0.040 ↑ 1.0 40 1

Result (cost=0.42..5.65 rows=41 width=43) (actual time=0.017..0.040 rows=40 loops=1)

  • Buffers: shared hit=6
40. 0.004 0.035 ↑ 1.0 40 1

Append (cost=0.42..5.24 rows=41 width=11) (actual time=0.016..0.035 rows=40 loops=1)

  • Buffers: shared hit=6
41. 0.031 0.031 ↑ 1.0 40 1

Index Only Scan using unique_daypart_params on daypartparameters daypartparameters_4 (cost=0.42..5.24 rows=41 width=11) (actual time=0.016..0.031 rows=40 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 25))
  • Heap Fetches: 0
  • Buffers: shared hit=6
42.          

CTE cold_program

43. 0.005 0.036 ↑ 1.1 40 1

Result (cost=0.42..5.68 rows=42 width=43) (actual time=0.021..0.036 rows=40 loops=1)

  • Buffers: shared hit=4
44. 0.004 0.031 ↑ 1.1 40 1

Append (cost=0.42..5.26 rows=42 width=11) (actual time=0.020..0.031 rows=40 loops=1)

  • Buffers: shared hit=4
45. 0.027 0.027 ↑ 1.1 40 1

Index Only Scan using unique_daypart_params on daypartparameters daypartparameters_5 (cost=0.42..5.26 rows=42 width=11) (actual time=0.020..0.027 rows=40 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 26))
  • Heap Fetches: 0
  • Buffers: shared hit=4
46.          

CTE demand_sub

47. 0.048 0.534 ↓ 2.0 4 1

GroupAggregate (cost=288.03..288.13 rows=2 width=121) (actual time=0.502..0.534 rows=4 loops=1)

  • Group Key: demand_wo.aired_date, demand_wo.daypart_placed, dim_date.broadcast_year_number, dim_date.broadcast_week_number, dim_date.broadcast_week_day_number, dim_date.broadcast_week_day_name, demand_wo.stationid, (CASE WHEN ((demand_wo.break_code)::text = ANY ('{"ADJ TWIN",TWIN}'::text[])) THEN true ELSE false END)
  • Buffers: shared hit=257
48. 0.060 0.486 ↓ 36.0 72 1

Sort (cost=288.03..288.03 rows=2 width=73) (actual time=0.481..0.486 rows=72 loops=1)

  • Sort Key: demand_wo.aired_date, dim_date.broadcast_year_number, dim_date.broadcast_week_number, dim_date.broadcast_week_day_number, dim_date.broadcast_week_day_name, (CASE WHEN ((demand_wo.break_code)::text = ANY ('{"ADJ TWIN",TWIN}'::text[])) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=257
49. 0.061 0.426 ↓ 36.0 72 1

Nested Loop (cost=0.29..288.02 rows=2 width=73) (actual time=0.117..0.426 rows=72 loops=1)

  • Buffers: shared hit=251
50. 0.008 0.293 ↓ 36.0 72 1

Append (cost=0.00..271.41 rows=2 width=79) (actual time=0.103..0.293 rows=72 loops=1)

  • Buffers: shared hit=35
51. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on demand_wo (cost=0.00..0.00 rows=1 width=138) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND ((order_type)::text <> 'P'::text) AND (aired_dow = 6) AND ((daypart_placed)::text = 'MD'::text) AND (stationid = '187'::smallint) AND ((is_preempt)::integer = 0))
52. 0.284 0.284 ↓ 72.0 72 1

Index Scan using demand_wo_187_temp_aired_date_order_type_aired_dow_daypart_idx1 on demand_wo_187 (cost=0.42..271.41 rows=1 width=20) (actual time=0.102..0.284 rows=72 loops=1)

  • Index Cond: ((aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND (aired_dow = 6) AND ((daypart_placed)::text = 'MD'::text))
  • Filter: (((order_type)::text <> 'P'::text) AND (stationid = '187'::smallint) AND ((is_preempt)::integer = 0))
  • Rows Removed by Filter: 18
  • Buffers: shared hit=35
53. 0.072 0.072 ↑ 1.0 1 72

Index Scan using unique_date on dim_date (cost=0.29..8.30 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=72)

  • Index Cond: (full_date = demand_wo.aired_date)
  • Buffers: shared hit=216
54.          

CTE oversold_bump

55. 0.004 0.047 ↑ 1.2 40 1

Append (cost=0.42..127.41 rows=47 width=15) (actual time=0.023..0.047 rows=40 loops=1)

  • Buffers: shared hit=43
56. 0.043 0.043 ↑ 1.2 40 1

Index Scan using unique_daypart_params on daypartparameters daypartparameters_6 (cost=0.42..127.41 rows=47 width=15) (actual time=0.023..0.043 rows=40 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 14))
  • Buffers: shared hit=43
57.          

CTE pitch_rate

58. 0.012 0.012 ↑ 1.0 1 1

Index Scan using unique_station_params on stationparameters stationparameters_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 11))
  • Buffers: shared hit=3
59. 3.083 6.648 ↓ 3.0 3 1

Nested Loop (cost=0.71..538.21 rows=1 width=387) (actual time=3.703..6.648 rows=3 loops=1)

  • Join Filter: ((dim_calendar.calendar_broadcast_year = dim_calendar_1.calendar_broadcast_year) AND (dim_calendar.calendar_broadcast_week = dim_calendar_1.calendar_broadcast_week))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=1,181 read=1
60. 0.559 3.565 ↓ 3.0 3 1

Nested Loop (cost=0.71..187.62 rows=1 width=395) (actual time=2.878..3.565 rows=3 loops=1)

  • Join Filter: (capacity_sub.aired_date = dim_calendar.calendar_date)
  • Buffers: shared hit=683 read=1
61. 0.016 3.006 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..24.39 rows=1 width=395) (actual time=2.720..3.006 rows=3 loops=1)

  • Join Filter: (capacity_sub.stationid = pitch_rate.stationid)
  • Buffers: shared hit=521 read=1
62. 0.076 2.990 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..24.35 rows=1 width=363) (actual time=2.707..2.990 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = oversold_bump.stationid) AND ((capacity_sub.daypart)::text = (oversold_bump.daypart)::text) AND (oversold_bump.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (oversold_bump.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=518 read=1
63. 0.545 2.914 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..23.15 rows=1 width=346) (actual time=2.664..2.914 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = demand_sub.stationid) AND ((capacity_sub.daypart)::text = (demand_sub.daypart)::text) AND (capacity_sub.aired_date = demand_sub.aired_date) AND (capacity_sub.is_twin = demand_sub.is_twin))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=475 read=1
64. 0.062 2.369 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..23.08 rows=1 width=314) (actual time=2.146..2.369 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = cold_program.stationid) AND ((capacity_sub.daypart)::text = (cold_program.daypart)::text) AND (cold_program.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (cold_program.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=218 read=1
65. 0.070 2.307 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..22.01 rows=1 width=282) (actual time=2.111..2.307 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = hot_program.stationid) AND ((capacity_sub.daypart)::text = (hot_program.daypart)::text) AND (hot_program.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (hot_program.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=214 read=1
66. 0.071 2.237 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..20.96 rows=1 width=250) (actual time=2.078..2.237 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = ten_rate.stationid) AND ((capacity_sub.daypart)::text = (ten_rate.daypart)::text) AND (ten_rate.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (ten_rate.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=208 read=1
67. 0.068 2.166 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..19.74 rows=1 width=230) (actual time=2.045..2.166 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = fifteen_rate.stationid) AND ((capacity_sub.daypart)::text = (fifteen_rate.daypart)::text) AND (fifteen_rate.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (fifteen_rate.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=173 read=1
68. 0.081 2.098 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..18.52 rows=1 width=210) (actual time=2.010..2.098 rows=3 loops=1)

  • Join Filter: ((capacity_sub.stationid = thirty_rate.stationid) AND ((capacity_sub.daypart)::text = (thirty_rate.daypart)::text) AND (thirty_rate.is_twin = capacity_sub.is_twin) AND (CASE WHEN (capacity_sub.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (thirty_rate.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=136 read=1
69. 1.629 2.017 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..17.30 rows=1 width=190) (actual time=1.963..2.017 rows=3 loops=1)

  • Buffers: shared hit=97 read=1
70. 0.217 0.388 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..8.82 rows=1 width=187) (actual time=0.349..0.388 rows=3 loops=1)

  • Join Filter: (((rate_recommendation.daypart)::text = (rate_multi.daypart)::text) AND (rate_recommendation.stationid = rate_multi.stationid) AND (rate_recommendation.is_twin = rate_multi.is_twin) AND (CASE WHEN (rate_recommendation.aired_dow = ANY ('{6,7}'::integer[])) THEN 'WE'::text ELSE 'WD'::text END = (rate_multi.wd_we)::text))
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=85
71. 0.019 0.171 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..8.47 rows=1 width=157) (actual time=0.139..0.171 rows=3 loops=1)

  • Join Filter: (rate_recommendation.stationid = rate_cap.stationid)
  • Buffers: shared hit=31
72. 0.049 0.152 ↓ 3.0 3 1

Nested Loop (cost=0.00..8.44 rows=1 width=125) (actual time=0.125..0.152 rows=3 loops=1)

  • Buffers: shared hit=28
73. 0.103 0.103 ↓ 4.0 4 1

CTE Scan on capacity_sub (cost=0.00..0.10 rows=1 width=99) (actual time=0.095..0.103 rows=4 loops=1)

  • Filter: ((stationid = '187'::smallint) AND ((daypart)::text = 'MD'::text))