On Wed, 2025-02-26 at 15:27 +0100, large.goose2...@salomvary.com wrote: > I am working on optimizing a query that attempts to efficiently paginate > through a large table using multi-column "cursors" aka. the "seek method" > (as described in detail here: > https://use-the-index-luke.com/sql/partial-results/fetch-next-page). > > The table (drastically simplified) looks like this: > > CREATE TABLE data > ( > col_1 int NOT NULL, > col_2 int NOT NULL, > col_3 int NOT NULL, > content varchar(10) NOT NULL > ); > > And has an appropriate index: > > CREATE INDEX data_index ON data (col_1, col_2, col_3); > > The recommended query to paginate through this table is using the "row > values" syntax: > > SELECT content > FROM data > WHERE (col_1, col_2, col_3) > (10, 20, 29) > ORDER BY col_1, col_2, col_3 > LIMIT 100; > > Which results in a perfectly optimized query plan > > However, in reality, my query uses a mix of ascending and descending ordering > (with an > index matching the order columns), in which case the WHERE (col_1, col_2, > col_3) > (10, 20, 29) > syntax is not an option (unless I somehow derive "reversed" data from the > column, > which I would like to avoid).
Here are my ideas for this situation: https://www.cybertec-postgresql.com/en/keyset-pagination-with-descending-order/ Yours, Laurenz Albe
