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.

• Jasen: why not change the primary key to be (sensor,time) instead ? timestamptz is internally the same as bigint so will only be slightly less efficient than serial in the index,
• Bergi: @Jasen I'm kinda afraid that the timestamp is not unique enough. What is the resolution of the clock used for now()? Instead of a (big)serial my actual code uses txid_current() as part of the primary key, to prevent duplicate insertions by a single transaction - maybe my example is not really good. I made this question primarily to learn about indices.
• Jasen: one microsecond (or one millisecond on win-32 last time I looked)

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.

• Bergi: Thanks, that's all I wanted to know. I guess the example was choosen a bit unfortunate. Maybe a better showcase for the problem would be if each measurement consisted of a series of data points that are known to be monotonically increasing, i.e. the index within the measurement and the value are correlated.