agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedOptimizer Hint, to ignore limit and offset in optimizer plan
4+ messages / 4 participants
[nested] [flat]
* Optimizer Hint, to ignore limit and offset in optimizer plan
@ 2020-06-26 08:49 Martin Handsteiner <martin.handsteiner@sibvisions.com>
0 siblings, 3 replies; 4+ messages in thread
From: Martin Handsteiner @ 2020-06-26 08:49 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer Hint, to ignore limit and offset in optimizer plan
@ 2020-06-26 09:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: Martin Handsteiner <martin.handsteiner@sibvisions.com>
2 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2020-06-26 09:01 UTC (permalink / raw)
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer Hint, to ignore limit and offset in optimizer plan
@ 2020-06-26 23:59 Bruce Momjian <bruce@momjian.us>
parent: Martin Handsteiner <martin.handsteiner@sibvisions.com>
2 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2020-06-26 23:59 UTC (permalink / raw)
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer Hint, to ignore limit and offset in optimizer plan
@ 2020-06-27 11:08 Simon Riggs <simon@2ndquadrant.com>
parent: Martin Handsteiner <martin.handsteiner@sibvisions.com>
2 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2020-06-27 11:08 UTC (permalink / raw)
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-06-27 11:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-26 08:49 Optimizer Hint, to ignore limit and offset in optimizer plan Martin Handsteiner <martin.handsteiner@sibvisions.com>
2020-06-26 09:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-06-26 23:59 ` Bruce Momjian <bruce@momjian.us>
2020-06-27 11:08 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox