From: Mark Dalphin <mdalphin@sanger.otago.ac.nz>
To: pgsql-sql@postgresql.org
Subject: Sub-select problem (or bug?)
Date: Wed, 19 Aug 1998 03:42:58 +0000
Message-ID: <35DA49C1.9D1B536C@sanger.otago.ac.nz> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mdalphin@sanger.otago.ac.nz
Subject: Re: Sub-select problem (or bug?)
In-Reply-To: <35DA49C1.9D1B536C@sanger.otago.ac.nz>
References: <35DA49C1.9D1B536C@sanger.otago.ac.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox