Re: randomized order in select? - Mailing list pgsql-sql

From Tom Lane
Subject Re: randomized order in select?
Date
Msg-id 16599.1078987206@sss.pgh.pa.us
Whole thread Raw
In response to Re: randomized order in select?  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-sql
Bruno Wolff III <bruno@wolff.to> writes:
>   Enver ALTIN <enver.altin@frontsite.com.tr> wrote:
>> I wish I could do something like:
>> select tiptext from tips order by random limit 1
>> in PostgreSQL.

> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record.  The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1

If you do need to do this for a large table, I seem to recall that
we've previously worked out reasonable ways to select a random entry
efficiently using an index.  Dig around in the mail list archives for
details.
        regards, tom lane


pgsql-sql by date:

Previous
From: "Iain"
Date:
Subject: Re: randomized order in select?
Next
From: Oleg Bartunov
Date:
Subject: Re: Tsearch2 question: getting histogram of the vector elements