Skip site navigation (1) Skip section navigation (2)

Re: Selecting random rows efficiently

From: Jeff <threshar(at)torgo(dot)978(dot)org>
To: Richard Jones <rj(at)last(dot)fm>
Cc: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Selecting random rows efficiently
Date: 2003-08-30 13:08:51
Message-ID: Pine.BSF.4.44.0308300905390.829-100000@torgo.978.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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.

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



In response to

Responses

pgsql-performance by date

Next:From: Richard JonesDate: 2003-08-30 13:09:03
Subject: Selecting random rows efficiently
Previous:From: Ken GeisDate: 2003-08-30 07:38:13
Subject: Re: bad estimates

pgsql-hackers by date

Next:From: Richard JonesDate: 2003-08-30 13:09:03
Subject: Selecting random rows efficiently
Previous:From: Dan LangilleDate: 2003-08-30 12:49:07
Subject: Re: [HACKERS] What goes into the security doc?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group