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.

  • 1
    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,
    – Jasen
    Commented Jan 21, 2020 at 4:11
  • @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.
    – Bergi
    Commented Jan 21, 2020 at 13:14
  • one microsecond (or one millisecond on win-32 last time I looked)
    – Jasen
    Commented Jan 21, 2020 at 20:15

1 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.

  • 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.
    – Bergi
    Commented Jan 21, 2020 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.