Horton

Re: Removing unneeded self joins

Michael Paquier <michael@paquier.xyz>

View thread
From: Michael Paquier <michael@paquier.xyz>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, Andrei Lepikhov <lepihov@gmail.com>, Richard Guo <guofenglinux@gmail.com>, Ranier Vilela <ranier.vf@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@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-06-26T05:40:36Z
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 Sat, Apr 26, 2025 at 11:05:27PM +0300, Alexander Korotkov wrote:
>> I did some improvements to PHVs patch: revised comments and commit
>> message.  I'm going to push it if no objections.
> 
> Uh, v2 was there already.  That should be v3.

I was doing some work on pg_hint_plan, evaluating the amount of
breakages caused by v18.

Commit fc069a3a6319 (Implement Self-Join Elimination) is standing out
because it has basically broken the reliability of join_search_hook by
doing the self-join eliminations +before+ we have a chance to call the
hook on a joinlist in make_rel_from_joinlist().  For example, take a
query as simple as that:
SELECT * FROM t1 tab1, t1 tab2 WHERE tab1.c1 = tab2.c1;

Before this commit, it was possible to apply hints to each individual
aliases, driving the planner behavior, with levels_needed at 2 as an
effect of the join list having two members.  After this commit, the
self-join elimination does its job first, reduces the join list to 1,
removes the knowledge of the join and prevents any control that we had
before in this code path.

Perhaps it is fair to say that this new limitation in pg_hint_plan
should be documented and its tests reworked to avoid that, but it
seems to me that the change of behavior of join_search_hook could
also hurt some existing use cases.  I will not disagree that it may be
more useful to know that the joins are reduced when reaching the
modules while calling the hook, but cdf0231c88bd, that has introduced
the join_search_hook back in 2007 was used for a different reason than
the case I'm seeing as broken at the top of v18 today.

Anyway, it seems to me that we may need to do something here before
the release.  Note that if the consensus is "you should update your
module and not rely on the past behavior", I'm OK with that.  I just
wanted to raise the issue before this goes GA.  And well, I have a
pretty big pool of users that rely on this module, so..
--
Michael