24 ms·
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_l
by jmgimeno 7mo ago
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.
- dragon96 7mo agoIf severity is a low cardinality enum, it still seems acceptable
- mattashii 7mo agoThe order returned from the Index Scan is not the ordering requested by the user, so there would still have to be a full (or topk) Sort over the dataset returned from the index scan, which could negate the gains you get from using an Index Scan; PostgreSQL itself does not produce merge join plans that merge a spread of index scans to get suffix-ordered data out of an index.
- Cervisia 7mo agoThe SQLite documentation explains how (and how well) this works: https://www.sqlite.org/optoverview.html#the_skip_scan_optimization https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...
- igorw 7mo agoWhile Postgres did introduce skip scan in 18, it only works for equality matching: https://www.crunchydata.com/blog/get-excited-about-postgres-18#b-tree-skip-scans https://www.crunchydata.com/blog/get-excited-about-postgres-...
- Tostino 7mo agoDo a partial index on just timestamp where severity < 3 instead.