From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>
cc: Simon Riggs <simon@2ndquadrant.com>,
        "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
In-reply-to: 
 <VI1PR1001MB1423793F6E5624602BD2DEAFE86E0@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
References: 
 <VI1PR1001MB1423F5361320BB854708FD2FE8930@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
 <CANP8+jLYmta=WfEpbP-DihtNkD7VKhbEzMyOkYUjuBCvWEZfdw@mail.gmail.com>
 <VI1PR1001MB1423793F6E5624602BD2DEAFE86E0@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
Comments: In-reply-to Martin Handsteiner <martin.handsteiner@sibvisions.com>
	message dated "Mon, 29 Jun 2020 08:10:34 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <323665.1593441019.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 29 Jun 2020 10:30:19 -0400
Message-ID: <323666.1593441019@sss.pgh.pa.us>
Precedence: bulk

Martin Handsteiner <martin.handsteiner@sibvisions.com> writes:
> yes, this would exactly be the feature I was talking about.
> enable_costlimit =3D true (default) | false
> How high is the possibility, that this feature will find the way into th=
e postgres db?

Not very good, unless you can present a far more convincing use-case.

If I understand your desire, it is that issuing the "same" query in
different transactions would generate identical overall results
despite varying the offset/limit so as to fetch different parts of
that unchanging result.

The problem with this is that whether changing the offset/limit
changes the plan shape is just one of many reasons why you might
not get a consistent result --- the most unavoidable being that
other transactions might commit data changes.

Moreover, you insist that you shouldn't have to use an ORDER BY
to get these consistent results.  Sorry, but SQL is *defined* to
not produce consistent row ordering without ORDER BY.  Changing
that isn't a matter of some optimizer hint somewhere, it's a very
fundamental thing in many places.

I'd counsel taking another look at the suggestion made upthread
to use a cursor WITH HOLD.

			regards, tom lane