Feedback-ID: i35d941ae:Fastmail
MIME-Version: 1.0
Date: Mon, 17 Mar 2025 20:52:31 +0100
From: large.goose2829@salomvary.com
To: pgsql-performance@lists.postgresql.org
Message-Id: <8a919391-8510-442b-b020-f9a9f4e80fbb@app.fastmail.com>
In-Reply-To: <0bf497361892767b9e46df0928ed6554190ca1de.camel@cybertec.at>
References: <a60186fe-b552-42e3-824e-27dbdd312f15@app.fastmail.com>
 <0bf497361892767b9e46df0928ed6554190ca1de.camel@cybertec.at>
Subject: Re: Efficient pagination using multi-column cursors
Content-Type: multipart/alternative;
 boundary=6809a499245d4536b11ccba43bdc97b2
Archived-At: 
 <https://www.postgresql.org/message-id/8a919391-8510-442b-b020-f9a9f4e80fbb%40app.fastmail.com>
Precedence: bulk

--6809a499245d4536b11ccba43bdc97b2
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Folks, thanks everyone for the valuable inputs, I think I more-or-less u=
nderstand now what the options are for my particular problem.

On Wed, Feb 26, 2025, at 17:14, Peter Geoghegan wrote:
> On Wed, Feb 26, 2025 at 10:40=E2=80=AFAM <large.goose2829@salomvary.co=
m> wrote:
> > Does this mean that it is not possible to come up with a plan that h=
as the same performance as "WHERE (col_1, col_2, col_3) > (10, 20, 29)" =
using "handwritten" filters, or only for "mixed order"? Or not a theoret=
ical limitation but a limitation of the current implementation of the qu=
ery planner?
>=20
> Perhaps the query planner should be taught to rewrite the query in
> such a way as to make it unnecessary for you to do so -- I think that
> that's what MySQL is doing for you. That is beside the point.

Would it make sense to file a feature request for PostgreSQL to implemen=
t that MySQL-like optimization mentioned earlier?

On Wed, Feb 26, 2025, at 17:15, Laurenz Albe wrote:
> Here are my ideas for this situation:
>=20
> https://www.cybertec-postgresql.com/en/keyset-pagination-with-descendi=
ng-order/

Laurenz, your post is a goldmine of advanced solutions, thanks for shari=
ng.


Cheers,
M=C3=A1rton

--6809a499245d4536b11ccba43bdc97b2
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}=0Ap.MsoNormal,p.MsoNoSpacing{margin:0}</=
style></head><body><div>Folks, thanks everyone for the valuable inputs, =
I think I more-or-less understand now what the options are for my partic=
ular problem.<br></div><div><br></div><div><div>On Wed, Feb 26, 2025, at=
 17:14, Peter Geoghegan wrote:<br></div><blockquote type=3D"cite" id=3D"=
qt" style=3D""><div>On Wed, Feb 26, 2025 at 10:40=E2=80=AFAM &lt;<a href=
=3D"mailto:large.goose2829@salomvary.com">large.goose2829@salomvary.com<=
/a>&gt; wrote:<br></div><div>&gt; Does this mean that it is not possible=
 to come up with a plan that has the same performance as "WHERE (col_1, =
col_2, col_3) &gt; (10, 20, 29)" using "handwritten" filters, or only fo=
r "mixed order"? Or not a theoretical limitation but a limitation of the=
 current implementation of the query planner?<br></div><div><br></div><d=
iv>Perhaps the query planner should be taught to rewrite the query in<br=
></div><div>such a way as to make it unnecessary for you to do so -- I t=
hink that<br></div><div>that's what MySQL is doing for you. That is besi=
de the point.<br></div></blockquote><div><br></div><div>Would it make se=
nse to file a feature request for PostgreSQL to implement that MySQL-lik=
e optimization mentioned earlier?<br></div><div><br></div></div><div>On =
Wed, Feb 26, 2025, at 17:15, Laurenz Albe wrote:<br></div><blockquote ty=
pe=3D"cite" id=3D"qt" style=3D""><div>Here are my ideas for this situati=
on:<br></div><div><br></div><div><a href=3D"https://www.cybertec-postgre=
sql.com/en/keyset-pagination-with-descending-order/">https://www.cyberte=
c-postgresql.com/en/keyset-pagination-with-descending-order/</a><br></di=
v></blockquote><div><br></div><div>Laurenz, your post is a goldmine of a=
dvanced solutions, thanks for sharing.<br></div><div><br></div><div><div=
><div><div><br></div></div><div>Cheers,<br></div><div>M=C3=A1rton<br></d=
iv></div></div><div><br></div></body></html>
--6809a499245d4536b11ccba43bdc97b2--