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 DE1205299F for ; Tue, 28 Jun 2005 16:31:34 -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 27450-08 for ; Tue, 28 Jun 2005 19:31:26 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 6D168529B8 for ; Tue, 28 Jun 2005 16:31:24 -0300 (ADT) Received: (qmail 12757 invoked by uid 500); 28 Jun 2005 19:31:23 -0000 Date: Tue, 28 Jun 2005 14:31:23 -0500 From: Bruno Wolff III To: Merlin Moncure Cc: John A Meinel , Postgresql Performance , elein Subject: Re: tricky query Message-ID: <20050628193123.GA12571@wolff.to> Mail-Followup-To: Bruno Wolff III , Merlin Moncure , John A Meinel , Postgresql Performance , elein References: <6EE64EF3AB31D5448D0007DD34EEB3415C2BFF@Herge.rcsinc.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3415C2BFF@Herge.rcsinc.local> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/575 X-Sequence-Number: 13212 On Tue, Jun 28, 2005 at 12:02:09 -0400, Merlin Moncure wrote: > > Confirmed. Hats off to you, the above some really wicked querying. > IIRC I posted the same question several months ago with no response and > had given up on it. I think your solution (smallest X1 not in X) is a > good candidate for general bits, so I'm passing this to varlena for > review :) > > SELECT t1.id+1 as id_new FROM id_test t1 > WHERE NOT EXISTS > (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) > ORDER BY t1.id LIMIT 1; You need to rework this to check to see if row '1' is missing. The above returns the start of the first gap after the first row that isn't missing.