Re: SOLVED - RE: Poor performance using CTE

On Tue, Nov 20, 2012 at 4:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
>> On 15 November 2012 01:46, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> It cuts both ways. I have used CTEs a LOT precisely because this behaviour
>>> lets me get better plans. Without that I'll be back to using the "offset 0"
>>> hack.
>>
>> Is the "OFFSET 0" hack really so bad? We've been telling people to do
>> that for years, so it's already something that we've effectively
>> committed to.
>
> IMSNHO, 'OFFSET 0' is completely unreadable black magic.  I agree with
> Andrew: CTEs allow for manual composition of queries and can be the
> best tool when the planner is outsmarting itself.  In the old days,
> we'd extract data to a temp table and join against that: CTE are
> essentially a formalization of that technique.  I like things the way
> they are; if CTE are hurting your plan, that's an indication you're
> using them inappropriately.

I agree, **BUT**, I cannot imagine how pushing constraints to the CTE
(under adequate conditions) could be anything but beneficial.

It *could* just be a lack of imagination on my part. But if it were
not, then it'd be nice for it to be done automatically (since this
particular CTE behavior bites enough people already).