Re: Invalid optimization of VOLATILE function in WHERE clause?

On Wed, Sep 19, 2012 at 12:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane <tgl@sss.pgh.pa.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