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);
time and the
num are monotonously increasing, a row with higher
num value will also have a larger
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
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.