Re: Removing unneeded self joins

On Mon, Feb 24, 2025 at 2:22 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>
> On 24/2/2025 11:57, Alexander Korotkov wrote:
> > Hi, Andrei!
> >
> > Thank you for your feedback.
> >
> > On Mon, Feb 24, 2025 at 12:12 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> >> On 23/2/2025 22:15, Alexander Korotkov wrote:
> >>> There is my attempt to implement this approach. Getting rid of local
> >>> variable (and computation of the same value other way) required to
> >>> change arguments of remove_rel_from_eclass() as well. I'm going to
> >>> further polish this tomorrow.
> >> I passed through the patch. It works correctly.
> >>
> >> Multiple ifs in a single routine is not ideal.
> >
> > I would say there is a brachcing anyway. Even if it is inside
> > adjust_relid_set(). Patch makes it more explicit. Ideal or not, I
> > don't think it gets worse.
> >
> >> I have thought about it
> >> already, and it seems the remove_rel_from_query needs refactoring: when
> >> I first reused it for self-join removal, we didn't have the 'ojrelid'
> >> machinery, and it was implemented smoothly.
> >> Right now, this code contains multiple places where we need to remove
> >> the outer join relid and separating the removal code for the baserel and
> >> outer join may simplify the logic and make it safer.
> >> But the way to do it is not apparent now. May be if we implement a new
> >> technique of query tree reduction, the approach will become more evident.
> >
> > Could you, please, elaborate more on what you mean by "new technique
> > of query tree reduction"?
> I mean any transformations and optimisations that reduce search space
> for optimisation. Right now, I see the features reduce_unique_semijoins,
> remove_useless_joins, and remove_useless_self_joins.
> In practice, I see at least a join on a foreign key, where some cases
> potentially allow the removal of the JOIN operator.

Do you mean some generic facility, which generalizes all the
transformations you mentioned? If so, it would be cool. But how
could it look like?

------
Regards,
Alexander Korotkov
Supabase