PG IN() workarounds?

csn

Since IN() can be slow, what's a workaround for the following query?

select *
from table1
where id in (select id2 from table2);

I know IN's performance is being improved in the next version. Does anybody know what behind-the-scenes changes were implemented?

Sxooter

Often a join is a better choice, where your query could be:

select * from table1 join table2 on (table1.id=table2.id2)

Other than that, you kinda have to wait for 7.4 to go production, but I'm guessing it's pretty close. Beta3 should get cut in the next week or so, and I'm guessing beta 3 or 4 will be the last before rc1 (release candidate).

I'm playing with it (7.4beta2) on my workstation, and it's quite stable and works well.

csn

Looking forward to 7.4 🙂.

I thought I saw some posts/docs/something somewhere of IN() workarounds using "WHERE EXISTS", ANY/SOME, or similiar.

http://www.postgresql.org/docs/7.3/interactive/functions-subquery.html#AEN10449

csn

Ah, think it might have been something like this:

> Also, IN (subquery) is a known performance problem in PGSQL, at least if
the subquery is going to return > many rows.

It's too bad, since I'm rather fond of subqueries, but I avoid them like
the plague in PostgreSQL.

You're not really using a subquery - really just a long list of integers.
Subqueries are lightning fast, so long as you conver to the EXISTS form:

SELECT * FROM tab WHERE id IN (SELECT id2 FROM tab2);

converts to:

SELECT * FROM tab WHERE EXISTS (SELECT id2 FROM tab2 WHERE id2=id);

Chris

http://archives.postgresql.org/pgsql-performance/2003-05/msg00260.php