explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LgJP

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 4.889 ↑ 1.0 1 1

GroupAggregate (cost=891.54..1,429.26 rows=1 width=380) (actual time=4.889..4.889 rows=1 loops=1)

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

CTE capacity_sub

3. 0.017 0.062 ↑ 1.0 4 1

GroupAggregate (cost=15.15..15.28 rows=4 width=44) (actual time=0.055..0.062 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.012 0.045 ↑ 1.0 4 1

Sort (cost=15.15..15.16 rows=4 width=18) (actual time=0.044..0.045 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.033 ↑ 1.0 4 1

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

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

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

  • Buffers: shared hit=6
7. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on capacity_wo_non_distributed (cost=0.00..0.00 rows=1 width=19) (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 (aired_dow = 6) AND ((daypart_placed)::text = 'MD'::text) AND (stationid = '187'::smallint))
8. 0.029 0.029 ↓ 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.021..0.029 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 station_parameters

10. 0.006 0.032 ↑ 2.0 1 1

GroupAggregate (cost=8.58..14.76 rows=2 width=68) (actual time=0.032..0.032 rows=1 loops=1)

  • Group Key: stationparameters.stationid
  • Buffers: shared hit=6
11. 0.005 0.026 ↑ 1.0 2 1

Bitmap Heap Scan on stationparameters (cost=8.58..14.71 rows=2 width=12) (actual time=0.025..0.026 rows=2 loops=1)

  • Recheck Cond: ((stationid = 187) AND (paramid = ANY ('{11,13}'::integer[])))
  • Heap Blocks: exact=2
  • Buffers: shared hit=6
12. 0.021 0.021 ↑ 1.0 2 1

Bitmap Index Scan on unique_station_params (cost=0.00..8.58 rows=2 width=0) (actual time=0.021..0.021 rows=2 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = ANY ('{11,13}'::integer[])))
  • Buffers: shared hit=4
13.          

CTE daypart_multi

14. 0.018 0.103 ↑ 13.2 4 1

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

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

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

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

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=8
17. 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
18. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: ((stationid = 187) AND (daypart = 'MD'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=2
19. 0.012 0.012 ↓ 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.012..0.012 rows=0 loops=1)

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

CTE rate_multi

21. 0.044 0.208 ↑ 3.2 4 1

HashAggregate (cost=6.09..6.51 rows=13 width=159) (actual time=0.205..0.208 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
22. 0.012 0.164 ↓ 4.3 56 1

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

  • Buffers: shared hit=54
23. 0.108 0.108 ↓ 4.0 4 1

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

  • Filter: (stationid = 187)
  • Buffers: shared hit=14
24. 0.044 0.044 ↓ 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.011 rows=14 loops=4)

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

CTE daypart_parameters

26. 0.121 0.246 ↑ 6.8 40 1

HashAggregate (cost=520.00..522.74 rows=274 width=203) (actual time=0.229..0.246 rows=40 loops=1)

  • Group Key: daypartparameters_1.stationid, daypartparameters_1.daypart, daypartparameters_1.is_twin, daypartparameters_1.wd_we
  • Buffers: shared hit=41
27. 0.021 0.125 ↑ 1.2 234 1

Append (cost=29.15..509.04 rows=274 width=19) (actual time=0.037..0.125 rows=234 loops=1)

  • Buffers: shared hit=41
28. 0.046 0.075 ↑ 1.2 222 1

Bitmap Heap Scan on daypartparameters daypartparameters_1 (cost=29.15..453.67 rows=259 width=19) (actual time=0.037..0.075 rows=222 loops=1)

  • Recheck Cond: ((stationid = 187) AND (paramid = ANY ('{1,2,3,14,25,26}'::integer[])))
  • Heap Blocks: exact=10
  • Buffers: shared hit=32
29. 0.029 0.029 ↑ 1.2 222 1

Bitmap Index Scan on unique_daypart_params (cost=0.00..29.08 rows=259 width=0) (actual time=0.029..0.029 rows=222 loops=1)

  • Index Cond: ((stationid = 187) AND (paramid = ANY ('{1,2,3,14,25,26}'::integer[])))
  • Buffers: shared hit=22
30. 0.001 0.013 ↑ 1.2 4 1

Result (cost=4.32..18.46 rows=5 width=21) (actual time=0.012..0.013 rows=4 loops=1)

  • One-Time Filter: (1 = ANY ('{1,2,3,14,25,26}'::integer[]))
  • Buffers: shared hit=3
31. 0.004 0.012 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_3 (cost=4.32..18.46 rows=5 width=21) (actual time=0.011..0.012 rows=4 loops=1)

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

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

  • Index Cond: (stationid = 187)
  • Buffers: shared hit=2
33. 0.001 0.010 ↑ 1.2 4 1

Result (cost=4.32..18.46 rows=5 width=21) (actual time=0.009..0.010 rows=4 loops=1)

  • One-Time Filter: (2 = ANY ('{1,2,3,14,25,26}'::integer[]))
  • Buffers: shared hit=3
34. 0.004 0.009 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_4 (cost=4.32..18.46 rows=5 width=21) (actual time=0.008..0.009 rows=4 loops=1)

  • Recheck Cond: (stationid = 187)
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
35. 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
36. 0.000 0.006 ↑ 1.2 4 1

Result (cost=4.32..18.46 rows=5 width=21) (actual time=0.005..0.006 rows=4 loops=1)

  • One-Time Filter: (3 = ANY ('{1,2,3,14,25,26}'::integer[]))
  • Buffers: shared hit=3
37. 0.003 0.006 ↑ 1.2 4 1

Bitmap Heap Scan on derived_daypart_rate_diffs derived_daypart_rate_diffs_5 (cost=4.32..18.46 rows=5 width=21) (actual time=0.005..0.006 rows=4 loops=1)

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

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

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

CTE demand_sub

40. 0.046 0.506 ↓ 2.0 4 1

GroupAggregate (cost=288.03..288.13 rows=2 width=121) (actual time=0.475..0.506 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
41. 0.043 0.460 ↓ 36.0 72 1

Sort (cost=288.03..288.03 rows=2 width=73) (actual time=0.456..0.460 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
42. 0.060 0.417 ↓ 36.0 72 1

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

  • Buffers: shared hit=251
43. 0.007 0.285 ↓ 36.0 72 1

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

  • Buffers: shared hit=35
44. 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))
45. 0.277 0.277 ↓ 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.095..0.277 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
46. 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
47. 0.005 4.840 ↓ 3.0 3 1

Nested Loop (cost=0.71..538.09 rows=1 width=435) (actual time=2.092..4.840 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,042
48. 0.007 1.775 ↓ 3.0 3 1

Nested Loop (cost=0.71..187.50 rows=1 width=443) (actual time=1.264..1.775 rows=3 loops=1)

  • Join Filter: (capacity_sub.aired_date = dim_calendar.calendar_date)
  • Buffers: shared hit=544
49. 0.005 1.207 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..24.26 rows=1 width=443) (actual time=1.102..1.207 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=382
50. 0.005 0.689 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..24.19 rows=1 width=412) (actual time=0.611..0.689 rows=3 loops=1)

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

Nested Loop Left Join (cost=0.42..17.32 rows=1 width=222) (actual time=0.347..0.408 rows=3 loops=1)

  • Buffers: shared hit=90
52. 0.007 0.370 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..8.84 rows=1 width=219) (actual time=0.327..0.370 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=77
53. 0.005 0.150 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..8.50 rows=1 width=189) (actual time=0.114..0.150 rows=3 loops=1)

  • Join Filter: (rate_recommendation.stationid = station_parameters.stationid)
  • Buffers: shared hit=23
54. 0.004 0.112 ↓ 3.0 3 1

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

  • Buffers: shared hit=17
55. 0.068 0.068 ↓ 4.0 4 1

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

  • Filter: ((stationid = '187'::smallint) AND ((daypart)::text = 'MD'::text))
  • Buffers: shared hit=6
56. 0.008 0.040 ↑ 2.0 1 4

Append (cost=0.00..8.32 rows=2 width=26) (actual time=0.009..0.010 rows=1 loops=4)

  • Buffers: shared hit=11
57. 0.000 0.000 ↓ 0.0 0 4

Seq Scan on rate_recommendation (cost=0.00..0.00 rows=1 width=25) (actual time=0.000..0.000 rows=0 loops=4)

  • Filter: ((aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND (stationid = '187'::smallint) AND ((daypart)::text = 'MD'::text) AND ((order_type)::text = 'O'::text) AND (aired_dow = 6) AND (capacity_sub.aired_date = aired_date) AND (capacity_sub.is_twin = is_twin))
58. 0.032 0.032 ↑ 1.0 1 4

Index Scan using rate_recommendation_187_temp_aired_date_daypart_order_type_idx1 on rate_recommendation_187 (cost=0.29..8.32 rows=1 width=26) (actual time=0.008..0.008 rows=1 loops=4)

  • Index Cond: ((aired_date = capacity_sub.aired_date) AND (aired_date >= '2020-06-15'::date) AND (aired_date <= '2020-07-12'::date) AND ((daypart)::text = 'MD'::text) AND ((order_type)::text = 'O'::text) AND (is_twin = capacity_sub.is_twin))
  • Filter: ((stationid = '187'::smallint) AND (aired_dow = 6))
  • Buffers: shared hit=11
59. 0.033 0.033 ↑ 1.0 1 3

CTE Scan on station_parameters (cost=0.00..0.04 rows=1 width=68) (actual time=0.011..0.011 rows=1 loops=3)

  • Filter: (stationid = '187'::smallint)
  • Buffers: shared hit=6
60. 0.213 0.213 ↓ 4.0 4 3

CTE Scan on rate_multi (cost=0.00..0.33 rows=1 width=127) (actual time=0.069..0.071 rows=4 loops=3)

  • Filter: (((daypart)::text = 'MD'::text) AND (stationid = '187'::smallint))
  • Buffers: shared hit=54
61. 0.030 0.030 ↑ 1.0 1 3

Index Scan using unique_rate_override on raterecommendationoverride (cost=0.42..8.46 rows=1 width=23) (actual time=0.010..0.010 rows=1 loops=3)

  • Index Cond: ((rate_recommendation.stationid = stationid) AND (stationid = '187'::smallint) AND (rate_recommendation.aired_date = aired_date) AND ((rate_recommendation.daypart)::text = (daypart)::text) AND ((daypart)::text = 'MD'::text) AND ((rate_recommendation.order_type)::text = (order_type)::text) AND ((order_type)::text = 'O'::text) AND (rate_recommendation.is_twin = is_twin))
  • Filter: ((is_preempt)::integer = 0)
  • Buffers: shared hit=13
62. 0.276 0.276 ↓ 4.0 4 3

CTE Scan on daypart_parameters (cost=0.00..6.85 rows=1 width=287) (actual time=0.080..0.092 rows=4 loops=3)

  • Filter: ((stationid = '187'::smallint) AND ((daypart)::text = 'MD'::text))
  • Rows Removed by Filter: 36
  • Buffers: shared hit=41
63. 0.513 0.513 ↓ 4.0 4 3

CTE Scan on demand_sub (cost=0.00..0.05 rows=1 width=97) (actual time=0.159..0.171 rows=4 loops=3)

  • Filter: ((stationid = '187'::smallint) AND ((daypart)::text = 'MD'::text))
  • Buffers: shared hit=251
64. 0.561 0.561 ↑ 1.0 1 3

Index Scan using ix_dim_calendar_year_month on dim_calendar (cost=0.29..163.23 rows=1 width=12) (actual time=0.144..0.187 rows=1 loops=3)

  • Index Cond: (calendar_date = rate_recommendation.aired_date)
  • Buffers: shared hit=162
65. 3.060 3.060 ↑ 1.0 4 3

Seq Scan on dim_calendar dim_calendar_1 (cost=0.00..350.53 rows=4 width=8) (actual time=0.780..1.020 rows=4 loops=3)

  • Filter: (calendar_date = ANY ('{2020-06-15,2020-06-22,2020-06-29,2020-07-06}'::date[]))
  • Rows Removed by Filter: 12,421
  • Buffers: shared hit=498
Planning time : 47.941 ms
Execution time : 5.757 ms