I’d like timescale to allow creating chunk-aware indexes (for lack of better terms) and I think the example below is clear enough. Before logging a github issue requesting a feature, I’d like to understand if what I’m asking for is either possible already, or completely impossible.
Let’s create a basic hypertable and add a reporting-friendly ts_date column which could link to a date dimension. Its purpose is actually irrelevant, I have several similar columns included in all of my fact tables.
CREATE FUNCTION IMM_DIM_DATE_ID(TIMESTAMP) RETURNS INTEGER AS $$
SELECT TO_CHAR($1, 'YYYYMMDD')::INTEGER;
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
create table test (
ts_timestamptz TIMESTAMPTZ,
ts_date INTEGER GENERATED ALWAYS AS (IMM_DIM_DATE_ID(ts_timestamptz)) STORED
);
select create_hypertable('test', by_range('ts_timestamptz', INTERVAL '1 DAY'));
create index on test(ts_date);
INSERT INTO test (ts_timestamptz)
SELECT GENERATE_SERIES('2023-01-01'::DATE, '2023-01-15'::DATE, '1 SECOND'::INTERVAL)
Now that we have two weeeks of data, let’s compare two exact queries (in terms of data).
explain analyze
select * from test where ts_timestamptz BETWEEN '2023-01-05 00:00:00' AND '2023-01-05 23:59:59';
explain analyze
select * from test where ts_date = 20230105;
In the first select, only one specific chunk is scanned. Performance is great.
In the second select, all chunks are scanned, which is not great. The index does help of course. But it would be way better if that index was used the same way as the one created automatically by timescaledb. Chunk-aware as I said earlier…
Is this possible right now? If not, can this be done? Does this request already exist on github?