David Rowley <dgrowle...@gmail.com> writes: > I had another look at this and it appears you were right the first time, we > need to ensure there's no NULLs on both sides of the join condition.
Ugh. I'm back to being discouraged about the usefulness of the optimization. > The only other way I could imagine fixing this would be to have some other > sort of join type that always met the join condition if the right side of > the join had no tuples... Of course I'm not suggesting it gets implemented > this way, I'm just otherwise out of ideas. IIRC, we looked into implementing a true NOT IN join operator years ago. Not only is it messy as can be, but there are patents in the area :-(. So anything more than the most brain-dead-simple approach would be risky. I could see implementing a variant join operator in the hash join code, since there you get to look at the entire inner relation before you have to give any answers. You could easily detect both empty-inner and inner-contains-nulls and modify the results of matching appropriately. However, it's not apparent how that could be made to work for either mergejoin or nestloop-with-inner-index-scan, which greatly limits the usefulness of the approach. Worse yet, I think this'd be at best a marginal improvement on the existing hashed-subplan code path. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers