Should a time index be in ascending or descending order?
1
vote
1
answer
1461
views
Planning some new tables, I am trying to decide whether an index should be "ascending" or "descending".
The table will quite large (I imagine approx. 2000 inserts per minute, initially migrating from a different table with about 1 billion rows).
I will be using timescaledb extension for this (for partitioning by time).
This is how the table could be created:
create table "Sample"(
"id" bigserial,
"deviceId" int not null,
"timestamp" timestamptz not null,
"value" float8 not null
);
select create_hypertable('"Sample"', 'timestamp'); -- creates a desc index on "timestamp"
create index on "Sample"("deviceId", "timestamp"); -- should this be "desc"?
This is the two most common queries we'll be running (deviceId and timestamps may vary of course):
select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp"<'2024-01-01Z'
order by "timestamp" desc limit 1;
And
select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp" between '2024-01-01Z' and '2024-02-01Z'
order by "timestamp" asc;
So what I am trying to understand is in what order should the "timestamp" be? And why?
My (probably wrong) intuition tells me that the index with "timestamp" should be in ascending order, because I need to order the data by timestamp in *ascending* order.
But, the examples in the Timescale Documentation always index the time columns in descending order. I don't quite understand why.
What is the ideal choice of indices here?
Asked by birgersp
(175 rep)
Jun 5, 2024, 11:48 AM
Last activity: Jun 6, 2024, 06:39 AM
Last activity: Jun 6, 2024, 06:39 AM