The following bug has been logged online: Bug reference: 5059 Logged by: Kenaniah Cerny Email address: kenan...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs