From: | Tommy Gildseth <tommy(at)gildseth(dot)com> |
---|---|
To: | Sumeet <asumeet(at)gmail(dot)com> |
Cc: | Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: selecting random row values in postgres |
Date: | 2007-02-23 20:45:27 |
Message-ID: | 45DF5267.5090408@gildseth.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Geoff Tolley | 2007-02-24 03:33:28 | Re: selecting random row values in postgres |
Previous Message | Rajesh Kumar Mallah | 2007-02-23 20:07:31 | Re: selecting random row values in postgres |