Thread

Optimizer Hint, to ignore limit and offset in optimizer plan

Martin Handsteiner <martin.handsteiner@sibvisions.com> — 2020-06-26T08:49:19Z

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?

Thanks
  Martin

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

Pavel Stehule <pavel.stehule@gmail.com> — 2020-06-26T09:01:55Z

Hi

pá 26. 6. 2020 v 10:49 odesílatel Martin Handsteiner <
martin.handsteiner@sibvisions.com> napsal:

> 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?
>

There is an old trick (workaround) based on usage OFFSET 0.

you can use

SELECT * FROM (SELECT * FROM xxx OFFSET 0) s LIMIT 10;

In this case, a inner query isn't planned with LIMIT number.

Regards

Pavel


>
> Thanks
>
>   Martin
>

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

Bruce Momjian <bruce@momjian.us> — 2020-06-26T23:59:54Z

On Fri, Jun 26, 2020 at 08:49:19AM +0000, Martin Handsteiner 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.

Uh, have you considered WITH HOLD cursors:

	WITH HOLD specifies that the cursor can continue to be used after the
	transaction that created it successfully commits.  WITHOUT HOLD
	specifies that the cursor cannot be used outside of the transaction that
	created it. If neither WITHOUT HOLD nor WITH HOLD is specified, WITHOUT
	HOLD is the default.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

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

Simon Riggs <simon@2ndquadrant.com> — 2020-06-27T11:08:49Z

On Fri, 26 Jun 2020 at 09:49, Martin Handsteiner <
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

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

Martin Handsteiner <martin.handsteiner@sibvisions.com> — 2020-06-29T08:10:34Z

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

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

Tom Lane <tgl@sss.pgh.pa.us> — 2020-06-29T14:30:19Z

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

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

Erik Brandsberg <erik@heimdalldata.com> — 2020-06-29T14:33:43Z

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>

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

Bruce Momjian <bruce@momjian.us> — 2020-06-29T15:48:01Z

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

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

Martin Handsteiner <martin.handsteiner@sibvisions.com> — 2020-06-29T17:33:10Z

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

The use-case is saving memory and increase speed for showing data in applications.
This sounds simple, but please have a look at the scenarios and problems.

I'm aware, that an optimizer hint "enable_costlimit = true (default) | false" will not guarantee 100% transaction save data, of course.

This is also not the case by using an order by, there is still the same problem.
(for eg, forums are paged with limit, and ordered by creation date desc of the post. Switching to the second page (limit 10 offset 10) can show a post again, that was already on the first page, when there is a new post in the meanwhile.
But mainly every user can live with the result, as long as there are not completely nondeterministic results on paging)

In complex applications, there have to be shown a lot of data, sometimes really complex views.

Some Use Cases we have:
-  A complex, already tuned view that delivers the first rows of data in a view seconds. The user can page and view the next data, the paging will get slower, every page.
On a point, he has to define better search parameters, or the user can start a job that creates a full report without using the limit, but it takes time.
Very often the users are satisfied with the first pages. (They can work with the first pages of the result. On next day, due to their work they get again first pages on which they can work on)
Any order by will cause the view to take minutes, even if the first page will only show 10 rows, because the whole query has to be finished by the db and then ordered. This would not be practicable for any application, that the user has to wait minutes after a click...

- A view that delivers millions of result rows. The application has an sort on header functionality, where the user can sort on any column.
So there is no chance of setting a order by that will ensure a proper page order (eg sort on groupname).

If limit and offset would not be taken into account by optimizer, the above use cases would work acceptable. For transaction save problems, there has to always created a solution without limit and offset, of course. 
But exact the use cases where the limit and offset is needed (reducing memory and increase speed), the limit and offset clause causes non deterministic results for paging.

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

I will try, but I have found so far searching the internet, that the postgres jdbc driver does not support holdable cursors.
I have to check it by my own. 
Anyway, I was already told, that this belongs to the jdbc mailing list, and seams to be for now not an option as solution, if it is not supported by jdbc.

Regards
  Martin Handsteiner

-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us> 
Gesendet: Montag, 29. Juni 2020 16:30
An: Martin Handsteiner <martin.handsteiner@sibvisions.com>
Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-sql@lists.postgresql.org
Betreff: Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan

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