Re: Poor performance using CTE

On Tue, Nov 20, 2012 at 12:23 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Nov 20, 2012 at 9:10 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
>> On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>> The problem here is very clear.  Oracle is optimizing through the CTE.
>>>  PostgreSQL does not do this by design -- CTE's are used as a forced
>>> materialization step.
>>
>> While I love that design (it lets me solve lots of problems for huge
>> queries), wouldn't pushing constraints into the CTE be a rather safe
>> optimization?
>
> sure, or rewrite query as classic join.

I meant for postgres to do automatically. Rewriting as a join wouldn't
work as an optimization fence the way we're used to, but pushing
constraints upwards can only help (especially if highly selective).