Re: Pulling up sublink may break join-removal logic

On Wed, Apr 29, 2020 at 10:37 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

>
> On Wed, Apr 29, 2020 at 8:23 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
>> On Tue, 28 Apr 2020 at 19:04, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>> > I happened to notice $subject and not sure if it's an issue or not. When
>> > we're trying to remove a LEFT JOIN, one of the requirements is the inner
>> > side needs to be a single baserel. If there is a join qual that is a
>> > sublink and can be converted to a semi join with the inner side rel, the
>> > inner side would no longer be a single baserel and as a result the LEFT
>> > JOIN can no longer be removed.
>>
>> I think, in theory at least, that can be fixed by [1], where we no
>> longer rely on looking to see if the RelOptInfo has a unique index to
>> determine if the relation can duplicate outer side rows during the
>> join. Of course, they'll only exist on base relations, so hence the
>> check you're talking about. Instead, the patch's idea is to propagate
>> uniqueness down the join tree in the form of UniqueKeys.
>>
>
> Do you mean we're tracking the uniqueness of each RelOptInfo, baserel or
> joinrel, with UniqueKeys? I like the idea!
>

Yes, it is and welcome the for review for that patch:)

>
>> A quick glance shows there are a few implementation details of join
>> removals of why the removal still won't work with [1]. For example,
>> the singleton rel check causes it to abort both on the pre-check and
>> the final join removal check. There's also the removal itself that
>> assumes we're just removing a single relation. I'd guess that would
>> need to loop over the min_righthand relids with a bms_next_member loop
>> and remove each base rel one by one. I'd need to look in more detail
>> to know if there are any other limiting factors there.
>>
>
> Yeah, we'll have to teach remove_useless_joins to work with multiple
> relids.
>

You can see [1] for the discuss for this issue with UniqueKey respect.
search
"In the past we have some limited ability to detect the unqiueness after
join,
so that's would be ok. Since we have such ability now, this may be
another
opportunity to improve the join_is_removable function"

I'm checking it today and will have a feedback soon.

[1]
https://www.postgresql.org/message-id/CAKU4AWrGrs0Vk5OrZmS1gbTA2ijDH18NHKnXZTPZNuupn%2B%2Bing%40mail.gmail.com

Best Regards
Andy Fan