agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedAW: Optimizer Hint, to ignore limit and offset in optimizer plan
4+ messages / 4 participants
[nested] [flat]
* AW: Optimizer Hint, to ignore limit and offset in optimizer plan
@ 2020-06-29 08:10 Martin Handsteiner <martin.handsteiner@sibvisions.com>
2020-06-29 14:30 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Martin Handsteiner @ 2020-06-29 08:10 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Hello,
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?
Regards
Martin
Von: Simon Riggs <simon@2ndquadrant.com>
Gesendet: Samstag, 27. Juni 2020 13:09
An: Martin Handsteiner <martin.handsteiner@sibvisions.com>
Cc: pgsql-sql@lists.postgresql.org
Betreff: Re: Optimizer Hint, to ignore limit and offset in optimizer plan
On Fri, 26 Jun 2020 at 09:49, Martin Handsteiner <martin.handsteiner@sibvisions.com<mailto:martin.handsteiner@sibvisions.com>> wrote:
Hello,
I’m aware, that taking limit and offset into account of optimizer plan is not a bug.
Nevertheless it is very often an unwanted feature.
As the postgres db has the issue with not supporting cursors over commit/ rollback, it is necessary to use the limit and offset mechanism.
The problem now is, with either
* not always possible to ensure a proper sort (application with sort on header click),
* and also on complex queries and a lot of data, that will be slow when sorting the result.
So if there would be an optimizer hint, that tells the optimizer to ignore limit and offset on generating a plan, it would be perfect.
Mainly having the same optimizer plan without looking on limit and offset, the possibitlity of having mixed data is drastically reduced.
Is there possibly already an optimizer hint, to trickout the optimizer, and tell him, that I want all the data, even if there is for eg a limit 10 clause in the select?
It would be useful to have an additional optimizer flag, such as
enable_costlimit = true (default) | false
--
Simon Riggs http://www.2ndQuadrant.com/<http://www.2ndquadrant.com/;
Mission Critical Databases
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
2020-06-29 08:10 AW: Optimizer Hint, to ignore limit and offset in optimizer plan Martin Handsteiner <martin.handsteiner@sibvisions.com>
@ 2020-06-29 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-29 14:33 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Erik Brandsberg <erik@heimdalldata.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-06-29 14:30 UTC (permalink / raw)
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
2020-06-29 08:10 AW: Optimizer Hint, to ignore limit and offset in optimizer plan Martin Handsteiner <martin.handsteiner@sibvisions.com>
2020-06-29 14:30 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-06-29 14:33 ` Erik Brandsberg <erik@heimdalldata.com>
2020-06-29 15:48 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Brandsberg @ 2020-06-29 14:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Martin Handsteiner <martin.handsteiner@sibvisions.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
2020-06-29 08:10 AW: Optimizer Hint, to ignore limit and offset in optimizer plan Martin Handsteiner <martin.handsteiner@sibvisions.com>
2020-06-29 14:30 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Tom Lane <tgl@sss.pgh.pa.us>
2020-06-29 14:33 ` Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan Erik Brandsberg <erik@heimdalldata.com>
@ 2020-06-29 15:48 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2020-06-29 15:48 UTC (permalink / raw)
To: Erik Brandsberg <erik@heimdalldata.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Martin Handsteiner <martin.handsteiner@sibvisions.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Mon, Jun 29, 2020 at 10:33:43AM -0400, Erik Brandsberg wrote:
> 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.
You mean LIMIT/OFFSET without ORDER BY? I think issuing a warning,
perhaps when some "novice" mode is enabled, would work well.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-06-29 15:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-29 08:10 AW: Optimizer Hint, to ignore limit and offset in optimizer plan Martin Handsteiner <martin.handsteiner@sibvisions.com>
2020-06-29 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-29 14:33 ` Erik Brandsberg <erik@heimdalldata.com>
2020-06-29 15:48 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox