One of the few situations where I experience poor performance under PostgreSQL, compared to other, commercial databases, is when an EXISTS predicate is used. Actually, these often do perform quite well, but there are some situations where there are optimizations available which other products detect and use, which PostgreSQL misses. Looking back at the mailing list archives, it appears that optimizations similar to what other products use for both IN and EXISTS were added to the IN predicate circa 2003:
http://archives.postgresql.org/pgsql-hackers/2003-08/msg00438.php
-Make IN/NOT IN have similar performance to EXISTS/NOT EXISTS (Tom)
There are many situations where the EXISTS predicate and an IN predicate with a table subquery produce identical results. After reviewing the SQL standard and thinking about it a bit, I think that the equivalence holds unless:
(1) the equivalent IN predicate is capable of producing a result of UNKNOWN, and
(2) the predicate is used in a context where the difference between UNKNOWN and FALSE is significant.
One additional point: Martijn van Oosterhout pointed out in an earlier email that if the subquery contains any non-immutable functions there could be a difference, although he described that issue as "minor".
The most common case for UNKNOWN logical values is when comparisons involve a NULL on either or both sides of an operator. In some cases, such as the one I posted about a month ago, it is quickly clear to a human reader that none of the above conditions exist -- the columns are all NOT NULL (so the result of the comparisons can never be UNKNOWN), they are used in a context where UNKNOWN and FALSE produce the same results, and no non-immutable functions are invoked.
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01408.php
The plan for the EXISTS predicate (running in 8.2.3) has a cost of 72736.37, while the logically equivalent query using IN has a cost of 36.38 (and these do approximate reality), so if the faster option was visible to the planner, it would be chosen. Some would argue that I should just change the query to use IN. There are three problems with that.
(1) It requires the use of a multi-value row value constructor, which is a construct not supported by all databases we currently use. (We have a heterogeneous environment, where the same queries must run on multiple platforms.) We have a somewhat ugly but valid query to use as a workaround which runs on all of our databases; it has a PostgreSQL cost of 130.98, which is tolerable, if not optimal.
(2) I have seen a number of cases where the logically equivalent EXISTS predicate performs better than the IN predicate. The failure to recognize equivalence and to cost both approaches risks suboptimal performance. Avoiding that requires careful testing of both forms to coerce the planner into choosing the best plan.
(3) I have been trying to move our application programmers away from a focus on how they want to navigate through the data, toward declaring what they want as the result. (Some programmers routinely use cursors to navigate each table, row by row, based on what they think is the best plan, stuffing the data into a temporary table as they go, then selecting from the temporary table, when a single SELECT statement with a few subqueries will produce the desired data.) The current situation with these predicates diverts the focus from "what to show" back to "how to get it".
I hate to see any queries run slower on PostgreSQL than on other databases, so I'm suggesting we address this. We are talking about an optimization that I've seen in some other products for at least 15 years.
-Kevin