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 2E099529EB for ; Tue, 28 Jun 2005 17:39:11 -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 44671-01 for ; Tue, 28 Jun 2005 20:39:03 +0000 (GMT) Received: from dd01.profihoster.net (dd01.profihoster.net [80.86.168.71]) by svr1.postgresql.org (Postfix) with ESMTP id BF78D529B4 for ; Tue, 28 Jun 2005 17:39:01 -0300 (ADT) Received: from [192.168.54.33] (Ia1eb.i.pppool.de [85.73.161.235]) by dd01.profihoster.net (Postfix) with ESMTP id F19692381A3 for ; Tue, 28 Jun 2005 22:31:32 +0200 (CEST) Message-ID: <42C1B55E.40405@laliluna.de> Date: Tue, 28 Jun 2005 22:38:54 +0200 From: Sebastian Hennebrueder User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: de-DE, de, en-us, en MIME-Version: 1.0 Cc: pgsql-performance@postgresql.org Subject: Re: tricky query References: <6EE64EF3AB31D5448D0007DD34EEB3415C2BEC@Herge.rcsinc.local> <42C167C6.5070108@arbash-meinel.com> <42C16FE4.2020803@arbash-meinel.com> In-Reply-To: <42C16FE4.2020803@arbash-meinel.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/577 X-Sequence-Number: 13214 John A Meinel schrieb: > John A Meinel wrote: > >> > > Well, I was able to improve it to using appropriate index scans. > Here is the query: > > 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; > > I created a test table which has 90k randomly inserted rows. And this is > what EXPLAIN ANALYZE says: > > As Cosimo stated the result can be wrong. The result is always wrong when the id with value 1 does not exist. -- Best Regards / Viele Grüße Sebastian Hennebrueder ---- http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap.