Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
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: Sat, 30 Aug 2003 09:08:51 -0400 (EDT)
- Message-id: <Pine.BSF.4.44.0308300905390.829-100000@torgo.978.org> <text/plain>
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/
- Follow-Ups:
- Re: Selecting random rows efficiently
- From: Richard Jones
- Re: Selecting random rows efficiently
- References:
- Selecting random rows efficiently
- From: Richard Jones
- Selecting random rows efficiently
- Prev by Date: Selecting random rows efficiently
- Next by Date: Re: Selecting random rows efficiently
- Previous by thread: Selecting random rows efficiently
- Next by thread: Re: Selecting random rows efficiently
- Indexes: