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

On Fri, 26 Jun 2020 at 09:49, Martin Handsteiner <
martin(dot)handsteiner(at)sibvisions(dot)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