| 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 |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Huxton | 2002-11-19 14:36:19 | Re: Slow DELETE with IN clausule |
| Previous Message | Tom Lane | 2002-11-19 14:04:02 | Re: for/loop performance in plpgsql ? |