Re: Selecting random rows efficiently

From: Richard Jones <rj(at)last(dot)fm>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Selecting random rows efficiently
Date: 2003-08-30 14:25:51
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-performance

On Saturday 30 August 2003 1:08 pm, you wrote:
> 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.

Yes, the pkey is a SERIAL but the problem is that the sequence is rather

for example, it goes something like 1 -> 5000 then 100000->100000 and then

this is due to chunks being deleted etc..

if i pick a random number for the key it will not be a random enough
distribution, because the sequence is sparse.. sometimes it will pick a key
that doesnt exist.

i'm currently reading all the keys into an array and selecting randoms from
there - but this is no good long-term as i need to refresh the array of keys
to take into account newly added rows to the table (daily)

i was hoping there was some trickery with sequences that would allow me to
easily pick a random valid sequence number..?


> --
> Jeff Trout <jeff(at)jefftrout(dot)com>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-08-30 14:47:42 Re: Selecting random rows efficiently
Previous Message Dan Langille 2003-08-30 14:23:29 Re: [HACKERS] What goes into the security doc?

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-08-30 14:35:52 Re: bad estimates
Previous Message Ron Johnson 2003-08-30 14:04:01 Re: Selecting random rows efficiently