| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers pgsql-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/
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Jones | 2003-08-30 13:09:03 | Selecting random rows efficiently |
| Previous Message | Dan Langille | 2003-08-30 12:49:07 | Re: [HACKERS] What goes into the security doc? |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Jones | 2003-08-30 13:09:03 | Selecting random rows efficiently |
| Previous Message | Ken Geis | 2003-08-30 07:38:13 | Re: bad estimates |