The following bug has been logged online:
Bug reference: 5059
Logged by: Kenaniah Cerny
Email address: kenaniah@gmail.com
PostgreSQL version: 8.4.1
Operating system: Centos5.2
Description: Planner ignores estimates when planning an IN ()
subquery
Details:
Consider the following query:
http://pgsql.privatepaste.com/aa5DAtiwws
When planning the subquery of the IN () statement, the planner chose to scan
the indexes of the outer and inner columns in parallel using a nested loop
semi join.
http://pgsql.privatepaste.com/4eXj3zRcy7
By not enabling the planner to sort via the index of the outer column in the
WHERE clause (query above), the a nested loop version of the plan executes
in a fraction of the time.
http://pgsql.privatepaste.com/5c0bOcL3t6
As you can see from the above query, forcing the materialization of the
subquery produces a much superior plan.
http://pgsql.privatepaste.com/371nl6KFrI
For comparison, this query replaces the subquery with hard-coded values.
The planner appears to not be weighing the benefits of materializing the
subquery of the IN () statement properly when ordering is involved, and
still produces an inferior plan when ordering is not a factor.
Please feel free to contact me for additional test cases if needed.
Thanks,
Kenaniah