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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html