SELECT *
FROM benchmark_logs
WHERE severity < 3
ORDER BY timestamp DESC
LIMIT 10;
this index
CREATE INDEX ON benchmark_logs (severity, timestamp);
cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity < 3 and then walk the timestamps in descending order to get the top K rows."
Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.
The issue here is the row based format. You simply can't filter on arbitrary columns with that. Either use an external warehouse or a columnar plug-in like Timescale.
Maybe I'm wrong, but for this query:
SELECT * FROM benchmark_logs WHERE severity < 3 ORDER BY timestamp DESC LIMIT 10;
this index
CREATE INDEX ON benchmark_logs (severity, timestamp);
cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity < 3 and then walk the timestamps in descending order to get the top K rows."
Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.
The "But Wait, We Need Filters Too" paragraph mentions "US" filter which is introduced only later on.
And footnote 3 is unreferenced.
The issue here is the row based format. You simply can't filter on arbitrary columns with that. Either use an external warehouse or a columnar plug-in like Timescale.