CODEWITHSUNDEEP
timescaledb
John P
John P

Reputation: 1580

TimescaleDB - index on second timestamp column

I have a database table that looks like this:

CREATE TABLE metric_readings(
   event_time timestamptz not null,
   insert_time timestamptz not null,
   reading double not null
)
create hypertable('metric_readings','event_time');

The insert_time is when the data actually got added to the table (sometimes there is a big delay between when the data is generated and when it gets recorded). I'd like to be able to run efficient queries against the insert_time column for some analytics and data sync jobs. Is it possible to create an index on this table just on the insert_time column?

So something like this will be efficient

select * from metric_readings 
where insert_time > :range_start and insert_time < :range_end;

Upvotes: 0

Views: 193

Answers (1)

jonatasdp
jonatasdp

Reputation: 1412

By default, create_hypertable will create the indices. Check the params:

create_default_indexes BOOLEAN Whether to create default indexes on time/partitioning columns. Default is TRUE.

So, it already contains and use the index. You can also use EXPLAIN ANALYZE to confirm it.

You'll see chunk exclusion information.

If you're looking for a second index, you can add index and consider that if you don't know what chunk is there, probably it will walk through all the chunks, so combining with the time dimension is always a good idea.

Upvotes: 0

Related Questions