Re: Invalid optimization of VOLATILE function in WHERE clause?

On Wed, Sep 19, 2012 at 12:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> To do what you want, I'd suggest wrapping the join into a sub-select
>>> with an "OFFSET 0" clause, which will serve as an optimization fence
>>> that prevents the random() call from being pushed down.
>
>> You've repeatedly objected to complaints on pgsql-performance on the
>> grounds that WITH is an optimization fence. It seems awfully
>> inconsistent to turn around and say, oh, sometimes it's not a fence
>> after all.
>
> Huh? The join in question is not inside a WITH. If it were, that
> would work too, as noted by Merlin.

Oh, hmm. I see now: the problem isn't that random() is being pushed
into the WITH, it's that it's being pushed into the join. Sorry, I
should have read that more carefully.

It still seems like awfully weird behavior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company