Re: Efficient pagination using multi-column cursors

On Wed, 2025-02-26 at 15:27 +0100, large(dot)goose2829(at)salomvary(dot)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