Re: selecting random row values in postgres

Sumeet wrote:
> Thanks Buddy, really appreciate ur help on this
>
> ....problem solved...
>
> Is there any way this query can be optimized...i'm running it on a
> huge table with joins

ORDER BY rand() is rather slow on large datasets, since the db has to
actually generate a random value for each row in the table, before being
able use it to sort by. Preferable ways to do this include f.ex:
SELECT max(id) FROM table;
SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER
BY id LIMIT 1;

This means you need to execute 2 queries, and it can also be a good idea
to somehow cache the number of rows/largest ID of the table, for quicker
performence.
You can find an interesting discussion on this topic at
http://thedailywtf.com/Comments/Finding_Random_Rows.aspx (yeah, I know.
thedayilywtf.com isn't normally what I'd use as a reference for anything
:-) )

--
Tommy