I have a table of sensor observations with
obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day:
model_id int, hour_of_day int, model_val int.
To gap fill missing values, we would join these two tables through a crossover table that is:
sensor_id text, day_of_week int, model_id int
If our observations table is massive, what would be an optimal way of indexing it for joining on
hour. Does indexing a timestamp also index functions like
EXTRACT(isodow FROM obs_ts) or should I make those functional indexes explicit, e.g.
CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx). For joining on hour, would it be better to convert the
hour_of_day to a