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: Subselect in DELETE
Date: Mon, 24 Aug 1998 01:11:15 +0000
Message-ID: <35E0BDB3.70DD9AE3@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.

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



Message-ID: <35E0BDB3.70DD9AE3@sanger.otago.ac.nz>
Permalink:  ../35E0BDB3.70DD9AE3@sanger.otago.ac.nz/
Also on:    postgresql.org/message-id/35E0BDB3.70DD9AE3@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: Subselect in DELETE
  In-Reply-To: <35E0BDB3.70DD9AE3@sanger.otago.ac.nz>
  References: <35E0BDB3.70DD9AE3@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