From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | "'Feite Brekeveld'" <feite(dot)brekeveld(at)osiris-it(dot)nl>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: update ... from where id in (..) question |
Date: | 2001-05-02 19:26:46 |
Message-ID: | 01C0D31C.4CB550B0.mascarm@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
This FAQ Item 4.23:
4.23) Why are my subqueries using IN so slow?
Currently, we join subqueries to outer queries by sequentially
scanning the result of the subquery for each row of the outer query.
A workaround is to replace IN with EXISTS:
SELECT *
FROM tab
WHERE col1 IN (SELECT col2 FROM TAB2)
to:
SELECT *
FROM tab
WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
We hope to fix this limitation in a future release.
Mike Mascari
mascarm(at)mascari(dot)com
-----Original Message-----
From: Feite Brekeveld [SMTP:feite(dot)brekeveld(at)osiris-it(dot)nl]
Sent: Wednesday, May 02, 2001 4:31 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] update ... from where id in (..) question
Hi,
I have a table with approx. 2mln records.
There were a few for which I had to update statusfield, so I did:
update table set statusflag = 'U' where id in ('id10',
'id20',
'id30');
this took so long that I cancelled it, and used separate
update table set statusflag = 'U' where id = 'id10';
statements, which were executed in a fraction of a second.
Has someone an explanation for this ?
--
Feite Brekeveld
feite(dot)brekeveld(at)osiris-it(dot)nl
---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2001-05-02 19:33:36 | Re: Update Triggers & NULLs |
Previous Message | Aaron Brashears | 2001-05-02 19:23:14 | big pg 6.5 and 7.1 problem in simple application |