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.
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 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 3 479-7841
University of Otago FAX: +64 3 479-7866
Dunedin
New Zealand
