Index Optimization for Datetime comparing day of week and hour
Per Verace's suggestion I created a test table with 10M records. The TLDR: Indexes on datetime functions need to be explicit, any conversion of that datetime column for joining/filtering will not be served by an index.

SELECT obs_ts AS weekday_indexed, obs_ts AS hour_indexed , * 
INTO index_test
FROM observations
LIMIT 10000000;

CREATE INDEX ON index_test(EXTRACT('isodow' from weekday_indexed));
CREATE INDEX ON index_test(EXTRACT('hour' FROM hour_indexed));
CREATE INDEX ON index_test(obs_ts);
ANALYSE index_test;


Table size is 1.7 GB. All of the indexes are 214MB. From my tests the index on obs_ts is never used (probably because these comparisons involve changing the data-type of the timestamp column...). The function specific indexes are used, so there is definitely a performance-index size tradeoff.

Day of Week

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from obs_ts )=5
--3 secs 276 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from weekday_indexed)=5
--1 secs 586 msec.


Hour of Day

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from obs_ts )=5
-- Total query runtime: 2 secs 420 msec.

EXPLAIN ANALYZE SELECT COUNT(1)
FROM index_test
WHERE obs_ts::TIME &gt;= '05:00' AND obs_ts::TIME &lt; '06:00'
-- Total query runtime: 2 secs 391 msec.

SELECT COUNT(1)
FROM index_test
WHERE tx::TIME @&gt; timerange('05:00', '06:00')
-- Total query runtime: 1 secs 928 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from hour_indexed)=5
-- 779 msec.