From: Geoff Tolley <geoff@polimetrix.com>
To: Tommy Gildseth <tommy@gildseth.com>
Cc: Sumeet <asumeet@gmail.com>
Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: selecting random row values in postgres
Date: Fri, 23 Feb 2007 19:33:28 -0800
Message-ID: <45DFB208.1010201@polimetrix.com> (raw)
In-Reply-To: <45DF5267.5090408@gildseth.com>
References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com>
<a97c77030702231142h155654a0id3f357b7f4834447@mail.gmail.com>
<7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com>
<45DF5267.5090408@gildseth.com>
Tommy Gildseth wrote:
> Sumeet wrote:
>> Thanks Buddy, really appreciate ur help on this
>>
>> ....problem solved...
>>
>> Is there any way this query can be optimized...i'm running it on a
>> huge table with joins
>
> ORDER BY rand() is rather slow on large datasets, since the db has to
> actually generate a random value for each row in the table, before being
> able use it to sort by. Preferable ways to do this include f.ex:
> SELECT max(id) FROM table;
> SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER
> BY id LIMIT 1;
I'd have thought that in most cases the slowness would be due more to the
sort than the cost of generating the random numbers. Your pair of queries
neatly avoid doing the sort, but it does raise the question, does it matter
if not all rows have the same chance of being picked? As an extreme example
in the above, if there are id's 1-100 and id = 100000 in the table, the
last one will almost always be picked.
I wonder if the random aggregate that Josh Berkus wrote could be adapted to
return a record type rather than a random value of a single column? Its big
advantage is that it requires no sorting and all entries are equiprobable.
Unfortunately I'm a bit short of time at this end of a Friday to do much
but give you the URL:
http://www.powerpostgresql.com/Random_Aggregate
HTH,
Geoff
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: geoff@polimetrix.com, tommy@gildseth.com, asumeet@gmail.com, mallah.rajesh@gmail.com
Subject: Re: selecting random row values in postgres
In-Reply-To: <45DFB208.1010201@polimetrix.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox