Re: Pulling up sublink may break join-removal logic

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.

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.

I'll link back here over on that thread to see if Andy would like to
take a quick look at it.

David

[1] https://www.postgresql.org/message-id/CAKU4AWoymsjbm5KDYbsko13GUfG57pX1QyC3Y8sDHyrfoQeyQQ@mail.gmail.com