On Sat, 30 Aug 2003, Richard Jones wrote: > Hi, > i have a table of around 3 million rows from which i regularly (twice a second > at the moment) need to select a random row from > > currently i'm doing "order by rand() limit 1" - but i suspect this is > responsible for the large load on my db server - i guess that PG is doing far > too much work just to pick one row. >
If you have an int id (aka serial) column then it is simple - just pick a random number between 1 and currval('id_seq')... or offset rand() limit 1 perhaps? since you want random ther eis no need to bother with an order and that'll save a sort. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster