Share single index for multiple linearly correlated columns

Question

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.

Answer 1

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.