Skip site navigation (1) Skip section navigation (2)

Slow DELETE with IN clausule

From: Jirka Novak <j(dot)novak(at)netsystem(dot)cz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow DELETE with IN clausule
Date: 2002-11-19 14:26:09
Message-ID: 3DDA4A01.5040403@netsystem.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2002-11-19 14:36:19
Subject: Re: Slow DELETE with IN clausule
Previous:From: Tom LaneDate: 2002-11-19 14:04:02
Subject: Re: for/loop performance in plpgsql ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group