Re: ORDER BY random() LIMIT 1 slowness

=?iso-8859-1?Q?SZUCS_G=E1bor?= <surrano@mailbox.hu> writes:
>> CREATE TABLE poetry ( rand SERIAL, ... );
>>
>> SELECT * FROM poetry WHERE rand = (
>> SELECT int8( curval( 'poetry_rand_seq') * random()));

> Mmmm... It usually doesn't work for me.

Yeah ... better would be

>> SELECT * FROM poetry WHERE rand = (
>> SELECT int8( (select last_value from poetry_rand_seq) * random()));

Personally though, I'd skip the sequence entirely and do

    create table poetry (...,
                 rand float8 default random());
    create index on poetry.rand

    select * from poetry where rand > random() order by rand limit 1;

A difficulty with either of these approaches is that the system won't
optimize comparisons involving random() into indexscans.  To get around
that, you'd have to hide the random() call inside a user-defined
function that is (bogusly) marked cachable (or in 7.3, "stable" would be
the best choice).  At the moment I think it'd also work to stick the
random() call inside a subselect, but the UDF approach is less likely to
get broken by future changes.

            regards, tom lane