I have a table with two columns whose values have 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 increasing monotonously, a row with more
num the value will also have greater
Postgres will create a btree index on the primary key columns. Can i say it too use the same index when searching for lines by their
time instead of their
num? A sin
SELECT * FROM measurements WHERE sensor = $1 AND time >= $2 ORDER BY time;
The resulting lines would have exactly the same order as if they were sorted by
Is there a way to let the optimizer know? I've seen many articles on column correlation statistics, most of them being linked in this StackOverflow topic, but multi-column statistics seem to only analyze dependencies between individual values and cannot not make a linear correlation.
I was hoping to get the same result as if I created another index on
sensor, time, but postgres only need to keep and store one index.