From: Tommy Gildseth <tommy@gildseth.com>
To: 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 21:45:27 +0100
Message-ID: <45DF5267.5090408@gildseth.com> (raw)
In-Reply-To: <7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com>
References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com>
<a97c77030702231142h155654a0id3f357b7f4834447@mail.gmail.com>
<7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com>
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;
This means you need to execute 2 queries, and it can also be a good idea
to somehow cache the number of rows/largest ID of the table, for quicker
performence.
You can find an interesting discussion on this topic at
http://thedailywtf.com/Comments/Finding_Random_Rows.aspx (yeah, I know.
thedayilywtf.com isn't normally what I'd use as a reference for anything
:-) )
--
Tommy
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: tommy@gildseth.com, asumeet@gmail.com, mallah.rajesh@gmail.com
Subject: Re: selecting random row values in postgres
In-Reply-To: <45DF5267.5090408@gildseth.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