On Thu, 16 Apr 2020 at 14:17, Andy Fan <zhihui.fan1213@gmail.com> wrote: > V6 also includes: > 1. Fix the comment misleading you mentioned above. > 2. Fixed a concern case for `relation_has_uniquekeys_for` function.
Over on [1], Richard highlights a problem in the current join removals lack of ability to remove left joins unless the min_righthand side of the join is a singleton rel. It's my understanding that the reason the code checks for this is down to the fact that join removals used unique indexed to prove the uniqueness of the relation and obviously, those can only exist on base relations. I wondered if you might want to look into a 0003 patch which removes that restriction? I think this can be done now since we no longer look at unique indexes to provide the proves that the join to be removed won't duplicate outer side rows.
Yes, I think that would be another benefit of UniqueKey, but it doesn't happen
until now. I will take a look of it today and fix it in a separated commit.
I have make it work locally, the basic idea is to postpone the join removal at
build_join_rel stage where the uniquekey info is well maintained. I will test
more to send a product-ready-target patch tomorrow.
# explain (costs off) select a.i from a left join b on a.i = b.i and b.j in (select j from c); QUERY PLAN --------------- Seq Scan on a (1 row)