logoalt Hacker News

jmgimenotoday at 7:16 AM2 repliesview on HN

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.


Replies

Cervisiatoday at 9:07 AM

The SQLite documentation explains how (and how well) this works: https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...

show 1 reply
dragon96today at 7:44 AM

If severity is a low cardinality enum, it still seems acceptable

show 1 reply