Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow DELETE with IN clausule

Slow DELETE with IN clausule

From
Jirka Novak
Date:
Hello,

   does have IN operator in WHERE clausule any "undocumented" slowdown?
I have tables:

CREATE TABLE A (
         pkA int NOT NULL
         num int
    ...
);

CREATE TABLE B (
         fkA int NOT NULL,
    ...
);

ALTER TABLE B ADD CONSTRAINT FK_B_fkB_A_pkA FOREIGN KEY (fkB) REFERENCES
A(pkA);

   I have 3000 records in A and same number in B. For each record A I
have one record in B.
   When I do:


DELETE FROM B WHERE fkB IN (SELECT pkA FROM A WHERE num=1)

   postgresql start working for 2-3 minutes. But select (from IN
clausule) SELECT pkA FROM A WHERE num=1 end in few seconds. The slowest
is this DELETE when IN SELECT returns no records.
   Does any have some idea whats wrong?

                            Jirka Novak

Re: Slow DELETE with IN clausule

From
Andrew Sullivan
Date:
On Tue, Nov 19, 2002 at 03:26:09PM +0100, Jirka Novak wrote:
> Hello,
>
>    does have IN operator in WHERE clausule any "undocumented" slowdown?
> I have tables:

It's not undocumented:

http://www.ca.postgresql.org/docs/faq-english.html#4.22

A


--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: Slow DELETE with IN clausule

From
Richard Huxton
Date:
On Tuesday 19 Nov 2002 2:26 pm, Jirka Novak wrote:
> Hello,
>
>    does have IN operator in WHERE clausule any "undocumented" slowdown?

Yes, but I thought it was documented somewhere. It's certainly been discussed
on the lists. Search the archives for IN/EXISTS. If you can rewrite your
query with an EXISTS clause you should see a big improvement.

http://archives.postgresql.org

--
  Richard Huxton
  Archonet Ltd