On Tue, Nov 20, 2012 at 3:53 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
I'm probably beating a dead horse ... but isn't this just a hint? Except that it's worse than a hint, because it's a hint in disguise and is undocumented. As far as I can tell, there's no use for "OFFSET 0" except to act as an optimizer fence.
It's clearly an important need, given the nature of the dialog above (and many others that have passed through this mailing list).
Why not make an explicit hint syntax and document it? I've still don't understand why "hint" is a dirty word in Postgres. There are a half-dozen or so ways in common use to circumvent or correct sub-optimal plans.
Craig James
Craig Ringer <craig@xxxxxxxxxxxxxxx> writes:
> On 11/21/2012 12:06 AM, Claudio Freire wrote:
>> I meant for postgres to do automatically. Rewriting as a join wouldn't
>> work as an optimization fence the way we're used to, but pushing
>> constraints upwards can only help (especially if highly selective).
> Because people are now used to using CTEs as query hints, it'd probably
> cause performance regressions in working queries. Perhaps more
> importantly, Pg would have to prove that doing so didn't change queries
> that invoked functions with side-effects to avoid changing the results
> of currently valid queries.
We could trivially arrange to keep the current semantics if the CTE
query contains any volatile functions (or of course if it's
INSERT/UPDATE/DELETE). I think we'd also need to not optimize if
it's invoked from more than one place in the outer query.
I think the more interesting question is what cases wouldn't be covered
by such a rule. Typically you need to use OFFSET 0 in situations where
the planner has guessed wrong about costs or rowcounts, and I think
people are likely using WITH for that as well. Should we be telling
people that they ought to insert OFFSET 0 in WITH queries if they want
to be sure there's an optimization fence?
I'm probably beating a dead horse ... but isn't this just a hint? Except that it's worse than a hint, because it's a hint in disguise and is undocumented. As far as I can tell, there's no use for "OFFSET 0" except to act as an optimizer fence.
It's clearly an important need, given the nature of the dialog above (and many others that have passed through this mailing list).
Why not make an explicit hint syntax and document it? I've still don't understand why "hint" is a dirty word in Postgres. There are a half-dozen or so ways in common use to circumvent or correct sub-optimal plans.
Craig James
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
