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

Hi

pá 26. 6. 2020 v 10:49 odesílatel Martin Handsteiner <
martin(dot)handsteiner(at)sibvisions(dot)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
>