On Wed, Sep 19, 2012 at 9:30 AM, Tom Lane <[email protected]> wrote:
> [email protected] (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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
