Re: [PATCH] Keeps tracking the uniqueness with UniqueKey

On Wed, Apr 29, 2020 at 8:34 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Wed, Apr 29, 2020 at 8:29 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
>> On Thu, 16 Apr 2020 at 14:17, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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)

Best Regard
Andy Fan