Horton

Re: Removing unneeded self joins

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: "Gregory Stark (as CFM)" <stark.cfm@gmail.com>, Michał Kłeczek <michal@kleczek.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-05-25T09:40:35Z
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.

Attachments

On 3/6/23 10:30, Michał Kłeczek wrote:
 > Hi All,
 >
 > I just wanted to ask about the status and plans for this patch.
 > I can see it being stuck at “Waiting for Author” status in several
 > commit tests.
 >
 > I think this patch would be really beneficial for us as we heavily use
 > views to structure out code.
 > Each view is responsible for providing some calculated values and 
they > are joined in a query to retrieve the full set of information.
 >
 > Not sure how the process works and how I could help (I am absolutely
 > not capable of helping with coding I am afraid - but could sponsor a
 > (small :) ) bounty to speed things up).

Yes, I am still working on this feature. Because of significant changes 
in the optimizer code which Tom & Richard had been doing last months, I 
didn't touch it for a while. But now this work can be continued.

Current patch is rebased on current master. Because of the nullable_rels 
logic, introduced recently, ojrelids were highly spreaded across planner 
bitmapsets. So, JE logic was changed.

But now, I'm less happy with the code. It seems we need to refactor it:
1. According to reports of some performance engineers, the feature can 
cause overhead ~0.5% on trivial queries without joins at all. We should 
discover the patch and find the way for quick and cheap return, if the 
statement contains no one join or, maybe stronger, no one self join.
2. During join elimination we replace clauses like 'x=x' with 'x IS NOT 
NULL'. It is a weak point because we change clause semantic 
(mergejoinable to non-mergejoinable, in this example) and could forget 
consistently change some RestrictInfo fields.
3. In the previous versions we changed the remove_rel_from_query routine 
trying to use it in both 'Useless outer join' and 'Self join' 
elimination optimizations. Now, because of the 'ojrelid' field it looks 
too complicated. Do we need to split this routine again?

-- 
Regards
Andrey Lepikhov
Postgres Professional