Received: from arwen.otago.ac.nz (arwen.otago.ac.nz [139.80.64.160]) by hub.org (8.8.8/8.7.5) with ESMTP id XAA08465 for ; Tue, 18 Aug 1998 23:43:26 -0400 (EDT) Received: from sanger.otago.ac.nz (uther.otago.ac.nz [139.80.16.156]) by arwen.otago.ac.nz (8.9.1/8.9.1) with ESMTP id PAA17106; Wed, 19 Aug 1998 15:43:12 +1200 (NZST) Sender: mdalphin@stonebow.otago.ac.nz Message-ID: <35DA49C1.9D1B536C@sanger.otago.ac.nz> Date: Wed, 19 Aug 1998 03:42:58 +0000 From: Mark Dalphin Organization: University of Otago X-Mailer: Mozilla 4.05 [en] (X11; I; Linux 2.0.32 i686) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Sub-select problem (or bug?) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I am having problems with the sub-select in PostgreSQL. As I have not tried to use these before, I am not sure if the problem is with me, SQL or PostgreSQL. I have two tables: CREATE TABLE tab1 ( locus char16, shortname char8 -- lots of other data ... ); -- This table flags some loci in tab1 as having errors. CREATE TABLE tab2 ( id oid, -- foreign key into TABLE tab1 errno int4 -- An error indicator ); If I say: SELECT * FROM tab1 WHERE shortname='AciSPP'; I retrieve 36 rows out of ~100,000 in seconds. This is okay. If I say: SELECT id, locus, errno FROM tab1, tab2 WHERE tab1.oid=tab2.id AND shortname='AciSPP'; I retrieve 22 rows out of the 100,000 in tab1 and 80,000 in tab2 in much less than a minute. This is also okay or even good! :-) Now, assume I wish to remove all traces of 'AciSPP' from my database. I need to remove those from within TABLE tab2 first. I tried this: DELETE FROM tab2 WHERE id in (SELECT oid FROM tab1 WHERE shortname='AciSPP'); This DELETE runs for over an hour and nothing happens. Except that the CPU usage runs very high. Am I doing this delete correctly? Is there some aspect of sub-selects (or 'IN') that I don't understand? Or is this a bug in the sub-selects. I saw a note in the archives from last May suggesting something may be wrong with sub-selects (or that they were slow), but it looked as though that was dependent on the sub-select containing a regular-expression search. System is: PostgreSQL 6.3.2 on a Dec Alpha running Digital UNIX. Thanks for your help, Mark Dalphin -- Dr. Mark Dalphin e-mail: mdalphin@sanger.otago.ac.nz Dept. of Biochemistry phone: +64 03 479-7841 University of Otago FAX: +64 03 479-7866 Dunedin, New Zealand