Postgres Pro
Downloads
Home   >   mailing lists

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

Optimizer Hint, to ignore limit and offset in optimizer plan

From
Martin Handsteiner
Date:

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

From
Pavel Stehule
Date:
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

From
Bruce Momjian
Date:
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

From
Simon Riggs
Date:
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/
Mission Critical Databases

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

From
Martin Handsteiner
Date:

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> 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/
Mission Critical Databases