On Wed, 2025-02-26 at 15:27 +0100, large.goose2829@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
