X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4790FD1B8C4 for ; Sat, 30 Aug 2003 12:15:39 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 12408-06 for ; Sat, 30 Aug 2003 09:15:08 -0300 (ADT) Received: from cmailm3.svr.pol.co.uk (cmailm3.svr.pol.co.uk [195.92.193.19]) by svr1.postgresql.org (Postfix) with ESMTP id CEABAD1B8C0 for ; Sat, 30 Aug 2003 09:15:01 -0300 (ADT) Received: from user-2040.bbd23tcl.dsl.pol.co.uk ([81.77.247.248] helo=rj) by cmailm3.svr.pol.co.uk with esmtp (Exim 4.14) id 19t4dA-0001R9-OD for pgsql-performance@postgresql.org; Sat, 30 Aug 2003 13:15:01 +0100 From: Richard Jones Reply-To: rj@last.fm Organization: Last.FM To: PgSQL Performance ML Subject: Selecting random rows efficiently Date: Sat, 30 Aug 2003 13:09:03 +0000 User-Agent: KMail/1.5.1 References: <200308300244.h7U2i4S12751@candle.pha.pa.us> <1062220877.669.406.camel@haggis> In-Reply-To: <1062220877.669.406.camel@haggis> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200308301309.03412.rj@last.fm> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/523 X-Sequence-Number: 3339 Hi, i have a table of around 3 million rows from which i regularly (twice a second at the moment) need to select a random row from currently i'm doing "order by rand() limit 1" - but i suspect this is responsible for the large load on my db server - i guess that PG is doing far too much work just to pick one row. one way i can think of is to read in all the primary keys from my table, and select one of the keys at random then directly fetch that row. are there any other ways to do this? i need to keep the load down :) Thanks, Richard