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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Martin Handsteiner <martin(dot)handsteiner(at)sibvisions(dot)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(at)heimdalldata(dot)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>