Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 406A99FC3B1 for ; Fri, 23 Feb 2007 16:45:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 27088-01 for ; Fri, 23 Feb 2007 16:45:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from twobarks.com (ns104.twobarks.com [80.65.59.26]) by postgresql.org (Postfix) with ESMTP id 042DB9FC383 for ; Fri, 23 Feb 2007 16:45:31 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by twobarks.com (Postfix) with ESMTP id E2614564023; Fri, 23 Feb 2007 21:45:38 +0100 (CET) Received: from twobarks.com ([127.0.0.1]) by localhost (ns104 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12640-09; Fri, 23 Feb 2007 21:45:38 +0100 (CET) Received: from [10.0.0.68] (cC2A55AC1.dhcp.bluecom.no [193.90.165.194]) by twobarks.com (Postfix) with ESMTP id 8DA8F564022; Fri, 23 Feb 2007 21:45:38 +0100 (CET) Message-ID: <45DF5267.5090408@gildseth.com> Date: Fri, 23 Feb 2007 21:45:27 +0100 From: Tommy Gildseth User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: Sumeet Cc: Rajesh Kumar Mallah , pgsql-sql@postgresql.org Subject: Re: selecting random row values in postgres References: <7539aebb0702231129s48399f26r8881c6eff81b0708@mail.gmail.com> <7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com> In-Reply-To: <7539aebb0702231156m3db743f8j50b445fb846881da@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at localhost X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.038 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/245 X-Sequence-Number: 27579 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