On Wed, Sep 19, 2012 at 9:30 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > florian.schoppm...@emc.com (Florian Schoppmann) writes: >> In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query > >> --8<-- >> WITH source AS ( >> SELECT i FROM generate_series(1,10) AS i >> ) >> SELECT >> i >> FROM >> source, ( >> SELECT >> count(*) AS _n >> FROM source >> ) AS _stats >> WHERE >> random() < 5::DOUBLE PRECISION/_n; >> -->8-- > > [ doesn't do what you think it should ] > > I can't get excited about this. Any time you put a volatile function > into WHERE, you're playing with fire. The docs warn against it: > http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL > > 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.
I like the more standard CTE approach to optimization fencing where it works: postgres=# WITH source AS ( SELECT i, random() r FROM generate_series(1,10) AS i ) SELECT i FROM source, ( SELECT count(*) AS _n FROM source ) AS _stats WHERE r < 5::DOUBLE PRECISION/_n; merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers