Re: SOLVED - RE: Poor performance using CTE

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.

merlin