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 40315D1B574 for ; Sat, 30 Aug 2003 17:43:54 +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 48057-06 for ; Sat, 30 Aug 2003 14:43:24 -0300 (ADT) Received: from mta01-svc.ntlworld.com (mta01-svc.ntlworld.com [62.253.162.41]) by svr1.postgresql.org (Postfix) with ESMTP id 61E8ED1B8B5 for ; Sat, 30 Aug 2003 14:43:22 -0300 (ADT) Received: from russell ([81.99.189.14]) by mta01-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with SMTP id <20030830174322.DXSD17074.mta01-svc.ntlworld.com@russell> for ; Sat, 30 Aug 2003 18:43:22 +0100 From: "Russell Garrett" To: "PgSQL Performance ML" Subject: Re: Selecting random rows efficiently Date: Sat, 30 Aug 2003 18:43:22 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-Reply-To: <1062260304.669.566.camel@haggis> Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/542 X-Sequence-Number: 3358 Considering that we'd have to index the random field too, it'd be neater in the long term to re-number the primary key. Although, being a primary key, that's foreign-keyed from absolutely everywhere, so that'd probably take an amusingly long time. ...and no we're not from Micronesia, we're from ever so slightly less exotic London. Though Micronesia might be nice... Russ (also from last.fm but without the fancy address) pgsql-performance-owner@postgresql.org wrote: > On Sat, 2003-08-30 at 09:01, Rod Taylor wrote: >>> i was hoping there was some trickery with sequences that would >>> allow me to easily pick a random valid sequence number..? >> >> I would suggest renumbering the data. >> >> ALTER SEQUENCE ... RESTART WITH 1; >> UPDATE table SET pkey = DEFAULT; >> >> Of course, PostgreSQL may have trouble with that update due to >> evaluation of the unique constraint immediately -- so drop the >> primary key first, and add it back after. > > And if there are child tables, they'd all have to be updated, too.