Message-ID: <0bf497361892767b9e46df0928ed6554190ca1de.camel@cybertec.at>
Subject: Re: Efficient pagination using multi-column cursors
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: large.goose2829@salomvary.com, pgsql-performance@lists.postgresql.org
Date: Wed, 26 Feb 2025 17:15:18 +0100
In-Reply-To: <a60186fe-b552-42e3-824e-27dbdd312f15@app.fastmail.com>
References: <a60186fe-b552-42e3-824e-27dbdd312f15@app.fastmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/0bf497361892767b9e46df0928ed6554190ca1de.camel%40cybertec.at>
Precedence: bulk

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).
>=20
> The table (drastically simplified) looks like this:
>=20
> CREATE TABLE data
> (
> =C2=A0=C2=A0=C2=A0 col_1=C2=A0=C2=A0 int=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 NOT NULL,
> =C2=A0=C2=A0=C2=A0 col_2=C2=A0=C2=A0 int=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 NOT NULL,
> =C2=A0=C2=A0=C2=A0 col_3=C2=A0=C2=A0 int=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 NOT NULL,
> =C2=A0=C2=A0=C2=A0 content varchar(10) NOT NULL
> );
>=20
> And has an appropriate index:
>=20
> CREATE INDEX data_index ON data (col_1, col_2, col_3);
>=20
> The recommended query to paginate through this table is using the "row va=
lues" syntax:
>=20
> SELECT content
> FROM data
> WHERE (col_1, col_2, col_3) > (10, 20, 29)
> ORDER BY col_1, col_2, col_3
> LIMIT 100;
>=20
> Which results in a perfectly optimized query plan
>=20
> However, in reality, my query uses a mix of ascending and descending orde=
ring (with an
> index matching the order columns), in which case the=C2=A0WHERE (col_1, c=
ol_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-or=
der/

Yours,
Laurenz Albe