Received: from neptun.sns-felb.debis.de (neptun.sns-felb.debis.de [53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBSAsgL97951 for ; Thu, 28 Dec 2000 05:54:42 -0500 (EST) (envelope-from jhartwig@debis.com) Received: by neptun.sns-felb.debis.de; id LAA14403; Thu, 28 Dec 2000 11:59:25 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma014380; Thu, 28 Dec 00 11:59:15 +0100 Received: from mail1.b3.dsh.de (localhost [127.0.0.1]) by dshmail1.dsh.de (8.9.1/8.8.7) with ESMTP id LAA10746 for ; Thu, 28 Dec 2000 11:56:28 +0100 (MET) Received: from debis.com ([138.201.143.121]) by mail1.b3.dsh.de (8.10.0/8.10.0) with ESMTP id eBSAsc509782 for ; Thu, 28 Dec 2000 11:54:38 +0100 Message-ID: <3A4B1C06.2A5C6C94@debis.com> Date: Thu, 28 Dec 2000 11:55:02 +0100 From: Jens Hartwig Organization: debis Systemhaus GEI X-Mailer: Mozilla 4.7 [de] (WinNT; I) X-Accept-Language: de MIME-Version: 1.0 To: PostgreSQL SQL Subject: Strange Execution-Plan for NOT EXISTS Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200012/275 X-Sequence-Number: 612 Hello all, I tried (just for academical fun) to resolve IDīs which are not used anymore, e.g.: ID NAME -- + ------------------ 1 | NOBODY 2 | ANYBODY 4 | EVERYBODY Now I want to get "3" as the next usable ID. I really know that this is kind of bad style but a friend of mine asked me if I had a solution for this problem. My solution would be the following statement: -------------------------------- select (min(id) + 1) from t_dummy d1 where not exists ( select id from t_dummy d2 where d2.id = (d1.id + 1) ); -------------------------------- The explain plan for this statement, tested against a real table with about 8,000 records in a freshly vacuumed database, looks like the following: Aggregate (cost=2924207.88..2924207.88 rows=1 width=12) -> Seq Scan on t_dummy d1 (cost=0.00..2924207.88 rows=1 width=12) SubPlan -> Seq Scan on t_dummy d2 (cost=0.00..331.36 rows=1 width=12 ) Why that? Wouldnīt it be possible to simple use the primary key index in the sub-query and exit from the outer query at the first occurence of an ID which has no following entry? Any ideas from anyone? Am I wrong? Best regards, Jens ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================