X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4A45C529B8 for ; Tue, 28 Jun 2005 12:43:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56783-04 for ; Tue, 28 Jun 2005 15:43:31 +0000 (GMT) Received: from sccmmhc92.asp.att.net (sccmmhc92.asp.att.net [204.127.203.212]) by svr1.postgresql.org (Postfix) with ESMTP id CB04452965 for ; Tue, 28 Jun 2005 12:43:30 -0300 (ADT) Received: from juju.arbash-meinel.com ([12.214.18.81]) by sccmmhc92.asp.att.net (sccmmhc92) with ESMTP id <20050628154330m92008ijtce>; Tue, 28 Jun 2005 15:43:30 +0000 Received: by juju.arbash-meinel.com (Postfix, from userid 505) id BE7F155FB6; Tue, 28 Jun 2005 10:43:29 -0500 (CDT) Received: from [192.168.1.11] (mail [192.168.1.1]) by juju.arbash-meinel.com (Postfix) with ESMTP id 34EC155FB6; Tue, 28 Jun 2005 10:43:25 -0500 (CDT) Message-ID: <42C1701D.5040102@arbash-meinel.com> Date: Tue, 28 Jun 2005 10:43:25 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Merlin Moncure Cc: Postgresql Performance Subject: Re: tricky query References: <6EE64EF3AB31D5448D0007DD34EEB3415C2BF8@Herge.rcsinc.local> In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3415C2BF8@Herge.rcsinc.local> X-Enigmail-Version: 0.92.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig7DA275E9AF29CA6DDFA18317" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/556 X-Sequence-Number: 13193 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig7DA275E9AF29CA6DDFA18317 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Merlin Moncure wrote: >>Not so bad. Try something like this: >> >>SELECT min(id+1) as id_new FROM table >> WHERE (id+1) NOT IN (SELECT id FROM table); >> >>Now, this requires probably a sequential scan, but I'm not sure how >> >> >you > > >>can get around that. >>Maybe if you got trickier and did some ordering and limits. The above >>seems to give the right answer, though. >> >> > >it does, but it is still faster than generate_series(), which requires >both a seqscan and a materialization of the function. > > > >>I don't know how big you want to scale to. >> >> > >big. :) > >merlin > > See my follow up post, which enables an index scan. On my system with 90k rows, it takes no apparent time. (0.000ms) John =:-> --------------enig7DA275E9AF29CA6DDFA18317 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCwXAdJdeBCYSNAAMRAqLMAJ9Ucer9XH+r7hnJ5C1PzwCaYTIA8QCfexE4 haXI6guAmd8mAo/OOQE8BAo= =H76W -----END PGP SIGNATURE----- --------------enig7DA275E9AF29CA6DDFA18317--