Re: Hints (was Poor performance using CTE)

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:

1. One is more likely to find it by looking up the documentation for SELECT

2. 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