pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



Message-ID: <35DA49C1.9D1B536C@sanger.otago.ac.nz>
Permalink:  ../35DA49C1.9D1B536C@sanger.otago.ac.nz/
Also on:    postgresql.org/message-id/35DA49C1.9D1B536C@sanger.otago.ac.nz

 · 

reply

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