Discussion

How We Optimized Top K in Postgres

davidelettieri: The "But Wait, We Need Filters Too" paragraph mentions "US" filter which is introduced only later on.

GrayShade: And footnote 3 is unreferenced.

jmgimeno: Maybe I'm wrong, but for this query:SELECT * FROM benchmark_logs WHERE severity < 3 ORDER BY timestamp DESC LIMIT 10;this indexCREATE 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.

tacone: 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.