Re: IN list processing performance (yet again)

Christopher Kings-Lynne wrote:

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.

Oops, you got that out of context, it was a different piece of conversation about subqueries in IN predicate,
not the scalar forms that was my overall discussion point.  You're right, I'm using lists of integers,
someone else was suggesting using subqueries in some context and I was responding to that.

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 

I hadn't thought of that, it's an excellent tip.  I'll have to remember it next time I want to use subqueries.
(Again, it's a side topic, my primary concern is scalar-form IN lists.)

Thanks,

Dave