pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Jones <rj@last.fm>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Selecting random rows efficiently
Date: Sat, 30 Aug 2003 14:25:51 +0000
Message-ID: <200308301425.51127.rj@last.fm> (raw)
In-Reply-To: <Pine.BSF.4.44.0308300905390.829-100000@torgo.978.org>
References: <Pine.BSF.4.44.0308300905390.829-100000@torgo.978.org>

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 
sparse

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

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..?

Thanks,
Rich.








>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster




view thread (10+ messages)  latest in thread

Message-ID: <200308301425.51127.rj@last.fm>
Permalink:  ../200308301425.51127.rj@last.fm/
Also on:    postgresql.org/message-id/200308301425.51127.rj@last.fm

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rj@last.fm
  Subject: Re: Selecting random rows efficiently
  In-Reply-To: <200308301425.51127.rj@last.fm>
  References: <Pine.BSF.4.44.0308300905390.829-100000@torgo.978.org> <200308301425.51127.rj@last.fm>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox