PS: We are too detecting that in some case swapping the predicate "exists (subselect)"
with "in (subselect)" is many times better. ????
This is an interesting observation. This is often true in Oracle.
But ... in versions of Postgres older than 7.4, the in (subselect) construct was unoptimized. The subselect would be fully evaluated, and then searched linearly to check if the in was satisifed. As you can imagine, this was very very slow for subselects returning many rows. exists was orders of magnitude faster.
AFAIK you're the first person to compare the execution speed of the new optimization in 7.4 with exists on a large set of real data. Can you post some actual numbers for us to look at?
Unfortunately we're still supporting 7.3 (which does not have the optimization) so will be in no hurry to switch from exists to the subselect form but when PG 7.5 comes out, we'll drop 7.3 support and may want to go rewrite some of the more important queries.
