> Yeah, this patch series is not yet quite up to the point of improving > that. That area is indeed the very next thing I want to work on, and > I did spend some effort on it last month, but I ran out of time to get > it working. Maybe we'll have something there for v17.
BTW, to clarify what's going on there: what I want to do is allow the regular equivalence-class machinery to handle deductions from equality operators appearing in LEFT JOIN ON clauses (maybe full joins too, but I'd be satisfied if it works for one-sided outer joins). I'd originally hoped that distinguishing pre-nulled from post-nulled variables would be enough to make that safe, but it's not. Here's an example:
select ... from t1 left join t2 on (t1.x = t2.y and t1.x = 1);
If we turn the generic equivclass.c logic loose on these clauses, it will deduce t2.y = 1, which is good, and then apply t2.y = 1 at the scan of t2, which is even better (since we might be able to turn that into an indexscan qual). However, it will also try to apply t1.x = 1 at the scan of t1, and that's just wrong, because that will eliminate t1 rows that should come through with null extension.
Is there a particular comment or README where that last conclusion is explained so that it makes sense. Intuitively, I would expect t1.x = 1 to be applied during the scan of t1 - it isn't like the output of the join is allowed to include t1 rows not matching that condition anyway.
IOW, I thought the more verbose but equivalent syntax for that was:
select ... from (select * from t1 as insub where insub.x = 1) as t1 left join t2 on (t1.x = t2.y)