|
On 28/11/12 15:17, Craig Ringer wrote:
On
27/11/2012 3:42 PM, Scott Marlowe wrote:
Here here! PostgreSQL is well known for
its extensibility and this is
the perfect place for hints.
I agree with the sentiment and your concerns. However, this
doesn't solve the CTE problem.
Some people are relying on the planner's inability to push
conditions into / pull conditions out of CTEs, and otherwise
re-arrange them. If support for optimising into eligible CTEs (ie
CTE terms that contain only SELECT or VALUES and call no VOLATILE
functions) then these applications will potentially encounter
serious performance regressions.
Should this feature never be added to Pg, making it different and
incompatible with other DBs that implement CTE optimisation, just
because some people are using it for a hacky hint like OFFSET 0?
Should these applications just be broken by the update, with
people told to add `OFFSET 0` or load some not-yet-existing hints
module after reporting the performance issue to the list?
I don't think either of those are acceptable. Sooner or later
somebody's going to want to add CTE optimisation, and I don't
think that "you can't" or "great, we'll do it and break
everything" are acceptable responses to any proposed patch someone
might come up with to add that.
A GUC might be OK, as apps can always SET it before problem
queries or not-yet-ported code. It'd probably reduce the rate at
which people fixed their code considerably, though, going by past
experience with standard_conforming_strings, etc, but it'd work.
--
Craig Ringer
I think it would be best to be something in
the SQL for SELECT, as:
- One is more likely to find it by looking up the documentation
for SELECT
- It could allow selective application within a SELECT: one
could have several queries within the WITH clause: where all
except one might benefit for optimisation, and the exception
might cause problems
I have suggested a couple possible syntax paterns, but there may
well be better alternative syntaxes.
Cheers,
Gavin
|
