RE: update ... from where id in (..) question

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@mascari.com

-----Original Message-----
From:    Feite Brekeveld [SMTP:feite.brekeveld@osiris-it.nl]
Sent:    Wednesday, May 02, 2001 4:31 AM
To:    pgsql-general@postgresql.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.brekeveld@osiris-it.nl



---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster