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