On Tue, 2007-01-30 at 17:34 -0500, Tom Lane wrote: > I think the NOT IN optimization that *would* be of use is to > automatically transform the NOT IN representation to an > outer-join-with-null-test type of operation, so as to give us a wider > choice of join methods. However, I'm not sure about correct handling > of NULLs on the RHS in such a scenario. The existing hashed-IN code > has to jump through some really ugly hoops to give spec-compliant > answers with NULLs.
ISTM that we can handle this neatly by looking for a WHERE clause that specifically excludes NULLs in the NOT IN. i.e. a query of the form select ... from LHS where key NOT IN (select key from RHS where key is not null) can be optimised to select ... from LHS left outer join RHS on LHS.key = RHS.key where RHS.key IS NULL; This rewards people that understand the spec-compliant behaviour and ensure there coding is watertight in the presence of NULLs. We can extend that behaviour later when we have plan invalidation to make it also pick up NOT NULL constraints on the keys of the RHS table. Doing it this way round is much more useful, since not all tables have NOT NULL constraints on their join columns so is slightly wider in its usefulness than just checking constraints. It's also very annoying in this specific case to not have any way for the SQL developer to pass information to the planner - and I don't mean hints. This would be similar to pull_up_IN_clauses() -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate