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