Re: Selecting a random row

IIRC, this was discussed a few times on this list, searching the
archives might get you some results. AFAIR, the only way to do it
efficiently is to have a column specially assigned for this purpose, and
populate it with random numbers in a big range. The column should be
indexed to assure fast access based on it. Then you can select the first
row with that column's value bigger (or smaller if you like) than a
random number in the same range.

HTH,
Csaba.

On Thu, 2004-11-04 at 14:34, Kari Lavikka wrote:
> Works but is too slooow. Shuffling whole table and selecting the first
> row is not the way to go in this case.
>
> Limit (cost=5340.74..5340.74 rows=1 width=4)
> -> Sort (cost=5340.74..5440.70 rows=39986 width=4)
> Sort Key: random()
> -> Seq Scan on users (cost=0.00..2284.37 rows=39986 width=4)
> Filter: (status = 'a'::bpchar)
>
> |\__/|
> ( oo ) Kari Lavikka - tuner(at)bdb(dot)fi
> __ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
> ""
>
> On Thu, 4 Nov 2004, Holger Klawitter wrote:
>
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Thursday 04 November 2004 12:36, Kari Lavikka wrote:
> > > Is there any explanation for this strange behavior or are there better
> > > ways to select a random row?
> >
> > How about
> >
> > SELECT ...whatever... ORDER BY random() LIMIT 1;
> >
> > Mit freundlichem Gruß / With kind regards
> > Holger Klawitter
> > - --
> > lists <at> klawitter <dot> de
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.2.2 (GNU/Linux)
> >
> > iD8DBQFBiibF1Xdt0HKSwgYRAlJXAJ4nUpDfKBKCigPVMt8WpKG4gZmt4wCcD/ZC
> > KHBlBl1+5FZ4pgqkZlyzWQA=
> > =MrrE
> > -----END PGP SIGNATURE-----
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org