On Tue, Jan 24, 2023 at 12:31 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> I wrote: > > Hans Buschmann <buschm...@nidsa.net> writes: > >> I just noticed your new efforts in this area. > >> I wanted to recurr to my old thread [1] considering constant > propagation of quals. > >> [1] > https://www.postgresql.org/message-id/1571413123735.26...@nidsa.net > > > 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) Thanks! David J.
