public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Efficient pagination using multi-column cursors
Date: Wed, 26 Feb 2025 17:15:18 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Wed, 2025-02-26 at 15:27 +0100, [email protected] 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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Efficient pagination using multi-column cursors
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox