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

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