explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yyzm

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

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

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

CTE capacity_sub

3. 0.016 0.068 ↑ 1.0 4 1

GroupAggregate (cost=15.15..15.28 rows=4 width=44) (actual time=0.061..0.068 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=6
4. 0.013 0.052 ↑ 1.0 4 1

Sort (cost=15.15..15.16 rows=4 width=18) (actual time=0.051..0.052 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=6
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=6
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=6
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=6
9.          

CTE rate_cap

10. 0.011 0.011 ↑ 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.011 rows=1 loops=1)

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

CTE daypart_multi

12. 0.019 0.105 ↑ 13.2 4 1

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

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

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

  • Buffers: shared hit=14
14. 0.047 0.047 ↑ 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.027..0.047 rows=38 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=8
15. 0.013 0.013 ↓ 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.013..0.013 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.032 0.199 ↑ 3.2 4 1

HashAggregate (cost=6.09..6.51 rows=13 width=159) (actual time=0.195..0.199 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.009 0.167 ↓ 4.3 56 1

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

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

CTE Scan on daypart_multi (cost=0.00..1.19 rows=1 width=127) (actual time=0.105..0.110 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.008..0.012 rows=14 loops=4)

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

CTE thirty_rate

24. 0.005 0.049 ↑ 1.3 38 1

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

  • Buffers: shared hit=39
25. 0.037 0.037 ↑ 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.020..0.037 rows=34 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = 1))
  • Buffers: shared hit=36
26. 0.001 0.007 ↑ 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.006..0.007 rows=4 loops=1)

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

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

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

CTE fifteen_rate

29. 0.004 0.043 ↑ 1.3 38 1

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

  • Buffers: shared hit=37
30. 0.033 0.033 ↑ 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.016..0.033 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.005 0.046 ↑ 1.3 38 1

Append (cost=0.42..137.73 rows=48 width=15) (actual time=0.009..0.046 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.009..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.041 ↑ 1.0 40 1

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

  • Buffers: shared hit=6
40. 0.005 0.036 ↑ 1.0 40 1

Append (cost=0.42..5.24 rows=41 width=11) (actual time=0.016..0.036 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.006 0.035 ↑ 1.1 40 1

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

  • Buffers: shared hit=4
44. 0.003 0.029 ↑ 1.1 40 1

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

  • Buffers: shared hit=4
45. 0.026 0.026 ↑ 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.018..0.026 rows=40 loops=1)

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

CTE demand_sub

47. 0.047 0.520 ↓ 2.0 4 1

GroupAggregate (cost=288.03..288.13 rows=2 width=121) (actual time=0.489..0.520 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=251
48. 0.043 0.473 ↓ 36.0 72 1

Sort (cost=288.03..288.03 rows=2 width=73) (actual time=0.468..0.473 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=251
49. 0.062 0.430 ↓ 36.0 72 1

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

  • Buffers: shared hit=251
50. 0.019 0.296 ↓ 36.0 72 1

Append (cost=0.00..271.41 rows=2 width=79) (actual time=0.093..0.296 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.276 0.276 ↓ 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.092..0.276 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.042 ↑ 1.2 40 1

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

  • Buffers: shared hit=43
56. 0.038 0.038 ↑ 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.018..0.038 rows=40 loops=1)

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

CTE pitch_rate

58. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

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

Nested Loop (cost=0.71..538.21 rows=1 width=387) (actual time=2.054..4.998 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,165
60. 0.569 1.926 ↓ 3.0 3 1

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

  • Join Filter: (capacity_sub.aired_date = dim_calendar.calendar_date)
  • Buffers: shared hit=667
61. 0.021 1.357 ↓ 3.0 3 1

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

  • Join Filter: (capacity_sub.stationid = pitch_rate.stationid)
  • Buffers: shared hit=505
62. 0.071 1.336 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..24.35 rows=1 width=363) (actual time=1.047..1.336 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=502
63. 0.532 1.265 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..23.15 rows=1 width=346) (actual time=1.009..1.265 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=459
64. 0.061 0.733 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..23.08 rows=1 width=314) (actual time=0.504..0.733 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=208
65. 0.069 0.672 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..22.01 rows=1 width=282) (actual time=0.470..0.672 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=204
66. 0.071 0.603 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..20.96 rows=1 width=250) (actual time=0.438..0.603 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=198
67. 0.069 0.532 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..19.74 rows=1 width=230) (actual time=0.406..0.532 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=163
68. 0.078 0.463 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..18.52 rows=1 width=210) (actual time=0.370..0.463 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=126
69. 0.040 0.385 ↓ 3.0 3 1

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

  • Buffers: shared hit=87
70. 0.210 0.345 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..8.82 rows=1 width=187) (actual time=0.305..0.345 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=74
71. 0.018 0.135 ↓ 3.0 3 1

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

  • Join Filter: (rate_recommendation.stationid = rate_cap.stationid)
  • Buffers: shared hit=20
72. 0.043 0.117 ↓ 3.0 3 1

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

  • Buffers: shared hit=17
73. 0.074 0.074 ↓ 4.0 4 1

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

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