On Sun, Jun 8, 2014 at 3:36 PM, David Rowley <dgrowle...@gmail.com> wrote: > Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS > queries and leaves NOT IN alone. The reason for this is because the values > returned by a subquery in the IN clause could have NULLs.
I believe the reason why this hasn't been done yet, is that the plan becomes invalid when another backend modifies the nullability of the column. To get it to replan, you'd have to introduce a dependency on the "NOT NULL" constraint, but it's impossible for now because there's no pg_constraint entry for NOT NULLs. The only way to consistently guarantee nullability is through primary key constraints. Fortunately that addresses most of the use cases of NOT IN(), in my experience. See the comment in check_functional_grouping: * Currently we only check to see if the rel has a primary key that is a * subset of the grouping_columns. We could also use plain unique constraints * if all their columns are known not null, but there's a problem: we need * to be able to represent the not-null-ness as part of the constraints added * to *constraintDeps. FIXME whenever not-null constraints get represented * in pg_constraint. The behavior you want seems somewhat similar to check_functional_grouping; maybe you could unify it with your targetListIsGuaranteedNotToHaveNulls at some level. (PS: that's one ugly function name :) Regards, Marti -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers