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 81C03D1B8C4 for ; Sat, 30 Aug 2003 13:32:04 +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 33601-01 for ; Sat, 30 Aug 2003 10:31:54 -0300 (ADT) Received: from cmailm4.svr.pol.co.uk (cmailm4.svr.pol.co.uk [195.92.193.211]) by svr1.postgresql.org (Postfix) with ESMTP id B4D22D1B8CE for ; Sat, 30 Aug 2003 10:31:48 -0300 (ADT) Received: from user-2040.bbd23tcl.dsl.pol.co.uk ([81.77.247.248] helo=rj) by cmailm4.svr.pol.co.uk with esmtp (Exim 4.14) id 19t5pU-0006Ab-WB for pgsql-performance@postgresql.org; Sat, 30 Aug 2003 14:31:49 +0100 From: Richard Jones Reply-To: rj@last.fm Organization: Last.FM To: PgSQL Performance ML Subject: Re: Selecting random rows efficiently Date: Sat, 30 Aug 2003 14:25:51 +0000 User-Agent: KMail/1.5.1 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200308301425.51127.rj@last.fm> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/525 X-Sequence-Number: 3341 On Saturday 30 August 2003 1:08 pm, you wrote: > On Sat, 30 Aug 2003, Richard Jones wrote: > > 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. > > If you have an int id (aka serial) column then it is simple - just pick a > random number between 1 and currval('id_seq')... > > or offset rand() limit 1 perhaps? > > since you want random ther eis no need to bother with an order and that'll > save a sort. Yes, the pkey is a SERIAL but the problem is that the sequence is rather sparse for example, it goes something like 1 -> 5000 then 100000->100000 and then 2000000->upwards this is due to chunks being deleted etc.. if i pick a random number for the key it will not be a random enough distribution, because the sequence is sparse.. sometimes it will pick a key that doesnt exist. i'm currently reading all the keys into an array and selecting randoms from there - but this is no good long-term as i need to refresh the array of keys to take into account newly added rows to the table (daily) i was hoping there was some trickery with sequences that would allow me to easily pick a random valid sequence number..? Thanks, Rich. > > -- > Jeff Trout > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster