I've got a table with two columns whose values have a perfect linear correlation, for example
CREATE TABLE measurements (
sensor int PRIMARY KEY,
num serial PRIMARY KEY,
time timestamptz DEFAULT now(),
value float
);
-- many times:
INSERT INTO measurements(sensor, value) VALUES ($1, $2);
Both the time and the num are monotonously increasing, a row with higher num value will also have a larger time value.
Postgres will create a btree index on the primary key columns. Can I somehow tell it to also use the same index when querying for rows by their time instead of by their num? As in
SELECT * FROM measurements WHERE sensor = $1 AND time >= $2 ORDER BY time;
The resulting rows would have exactly the same order as if sorted by num.
Is there a way to let the optimiser know? I've seen many articles on cross column correlation statistics, most of them linked in this StackOverflow topic, but the multi-column statistics seems to only analyze dependencies between individual values, and are unable to do a linear correlation.
I was hoping to achieve the same result as if I created another index on sensor, time, but have postgres need to maintain and store only a single index.
Best Answer
No, you cannot use an index on one column for searches on another column. I second Jasens comment that you should consider doing away with the generated integer and using the timestamp instead.