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

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 ?

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

On Wed, 2 May 2001, Feite Brekeveld wrote:

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 ?

1) Next time, try WHERE ID = 'id10' OR ID = 'id20' OR ... and see if
that's better.

2) Explanation for this? Have you tried EXPLAIN UPDATE table ....
Post the output of that, and someone might be able to help you decipher
it.

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Feite Brekeveld <feite.brekeveld@osiris-it.nl> writes:

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.

What postgres version? What does EXPLAIN show for the two queries?
Have you VACUUM ANALYZEd that table recently?

regards, tom lane

Joel Burton <jburton@scw.org> writes:

update table set statusflag = 'U' where id in ('id10', 'id20',
'id30');

1) Next time, try WHERE ID = 'id10' OR ID = 'id20' OR ... and see if
that's better.

The former should in fact be automatically translated to the latter,
so that's not the issue here.

regards, tom lane