Re: selecting random rows

On Thu, 11 Sep 2003, Joseph Shraibman wrote:

> Is there a way to get random rows besides ORDER BY random()?  The problem with ORDER BY
> random() is that is has to get all the rows from the table before the results are returned.

If you have a column that is a sequence of numbers with no holes, and you
already know the row count, you can get fairly fast random choices from it
with:

select * from accounts where aid = (select (floor(random()*10000)));

as long as the column has an index.

explain analyze select * from accounts where aid = (select
(floor(random()*10000)));
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on accounts  (cost=0.02..3177.02 rows=501 width=100) (actual
time=9.34..390.30 rows=1 loops=1)
   Filter: ((aid)::double precision = $0)
   InitPlan
     ->  Result  (cost=0.00..0.02 rows=1 width=0) (actual time=0.06..0.06
rows=1 loops=1)
 Total runtime: 390.48 msec

But the fastest way is to generate your random number in whatever code you
program your apps in (i.e. rand(0,rowcount-1) and use that number with
limit and offset or above if you have a sequential column with no holes in
it.

Really, it depends on how much you'll be doing it.  If it's to randomly
pick a banner ad for a website, then it's worth the extra effort to have
such a sequence in your table.  If it's a once a day kinda thing, then
performance probably isn't quite as big of an issue.