Re: Hints (was Poor performance using CTE)

On Tue, Nov 27, 2012 at 7:17 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> 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?

I'm strictly talking about any hinting mechanism being added being an
extension. Fixing the planner so that optimizations can get cross the
CTE boundary seems the domain of back end hackers, not extensions.