Horton

Re: Removing unneeded self joins

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alena Rybakina <a.rybakina@postgrespro.ru>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Robert Haas <robertmhaas@gmail.com>, Richard Guo <guofenglinux@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, "Gregory Stark (as CFM)" <stark.cfm@gmail.com>, Michał Kłeczek <michal@kleczek.org>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2025-02-26T12:14:31Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove GUC_NOT_IN_SAMPLE from enable_self_join_elimination

  2. Put enable_self_join_elimination into postgresql.conf.sample

  3. Get rid of ojrelid local variable in remove_rel_from_query()

  4. Implement Self-Join Elimination

  5. Revert: Remove useless self-joins

  6. Replace lateral references to removed rels in subqueries

  7. Replace relids in lateral subquery parse tree during SJE

  8. Forbid SJE with result relation

  9. Fix misuse of RelOptInfo.unique_for_rels cache by SJE

  10. Replace the relid in some missing fields during SJE

  11. Revert 56-bit relfilenode change and follow-up commits.

  12. Stabilize timetz test across DST transitions.

  13. Speed up finding EquivalenceClasses for a given set of rels

  14. Fix mark-and-restore-skipping test case to not be a self-join.

On Mon, Feb 24, 2025 at 2:22 PM Andrei Lepikhov <lepihov@gmail.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@gmail.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