Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan

New request:  If order by is not specified with an order by clause, allow
an option to be set that explicitly randomizes the results to break
everything that relies on the order.  :)  This would primarily be used for
QA work to find code that depends on an undefined order.

And waving hi at a fellow Yinzer!

On Mon, Jun 29, 2020 at 10:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Martin Handsteiner <martin.handsteiner@sibvisions.com> writes:
> > yes, this would exactly be the feature I was talking about.
> > enable_costlimit = true (default) | false
> > How high is the possibility, that this feature will find the way into
> the 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
>
>
>

-- 
*Erik Brandsberg*
erik@heimdalldata.com

www.heimdalldata.com
+1 (866) 433-2824 x 700
[image: AWS Competency Program]
<https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>