Re: Selecting random rows efficiently

Can you just create an extra serial column and make sure that one is
always in order and no holes in it?  (i.e. a nightly process, etc...)???

If so, then something like this truly flies:

select * from accounts where aid = (select cast(floor(random()*100000)+1 as int));

My times on it on a 100,000 row table are < 1 millisecond.

Note that you have to have a hole free sequence AND know how many rows
there are, but if you can meet those needs, this is screamingly fast.

On Sat, 30 Aug 2003, Russell Garrett wrote:

> Considering that we'd have to index the random field too, it'd be neater in
> the long term to re-number the primary key. Although, being a primary key,
> that's foreign-keyed from absolutely everywhere, so that'd probably take an
> amusingly long time.
>
> ...and no we're not from Micronesia, we're from ever so slightly less exotic
> London. Though Micronesia might be nice...
>
> Russ (also from last.fm but without the fancy address)
>
> pgsql-performance-owner@postgresql.org wrote:
> > On Sat, 2003-08-30 at 09:01, Rod Taylor wrote:
> >>> i was hoping there was some trickery with sequences that would
> >>> allow me to easily pick a random valid sequence number..?
> >>
> >> I would suggest renumbering the data.
> >>
> >> ALTER SEQUENCE ... RESTART WITH 1;
> >> UPDATE table SET pkey = DEFAULT;
> >>
> >> Of course, PostgreSQL may have trouble with that update due to
> >> evaluation of the unique constraint immediately -- so drop the
> >> primary key first, and add it back after.
> >
> > And if there are child tables, they'd all have to be updated, too.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>