Re: Selecting random rows efficiently - Mailing list pgsql-performance

From Ron Johnson
Subject Re: Selecting random rows efficiently
Date
Msg-id 1062252240.667.453.camel@haggis
Whole thread Raw
In response to Selecting random rows efficiently  (Richard Jones <rj@last.fm>)
List pgsql-performance
On Sat, 2003-08-30 at 08:09, 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.

What datatype is the selected by key?

Also, where is rand() defined?  Is that a UDF?

Could it be that there is a type mismatch?

> one way i can think of is to read in all the primary keys from my table, and
> select one of the keys at random then directly fetch that row.
>
> are there any other ways to do this? i need to keep the load down :)
>
> Thanks,
> Richard

Are you really in Micronesia?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"The greatest dangers to liberty lurk in insidious encroachment
by men of zeal, well-meaning, but without understanding."
Justice Louis Brandeis, dissenting, Olmstead v US (1928)


pgsql-performance by date:

Previous
From: Rod Taylor
Date:
Subject: Re: Selecting random rows efficiently
Next
From: Tom Lane
Date:
Subject: Re: bad estimates