Horton

Re: Removing unneeded self joins

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: David Steele <david@pgmasters.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2020-10-31T09:26:29Z
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

Thank you for this partial review, I included your changes:

On 9/23/20 9:23 AM, David Rowley wrote:
> On Fri, 3 Apr 2020 at 17:43, Andrey Lepikhov <a.lepikhov@postgrespro.ru> wrote:
> Doing thing the way I describe will allow you to get rid of all the
> UniqueRelInfo stuff.
Thanks for the review and sorry for the late reply.
I fixed small mistakes, mentioned in your letter.
Also I rewrote this patch at your suggestion [1].
Because of many changes, this patch can be viewed as a sketch.

To change self-join detection algorithm I used your delta patch from 
[2]. I added in the split_selfjoin_quals routine complex expressions 
handling for demonstration. But, it is not very useful with current 
infrastructure, i think.

Also I implemented one additional way for self-join detection algorithm: 
if the join target list isn't contained vars from inner relation, then 
we can detect self-join with only quals like a1.x=a2.y if check 
innerrel_is_unique is true.
Analysis of the target list is contained in the new routine - 
tlist_contains_rel_exprs - rewritten version of the build_joinrel_tlist 
routine.

Also changes of the join_is_removable() routine is removed from the 
patch. I couldn't understand why it is needed here.

Note, this patch causes change of one join.sql regression test output. 
It is not a bug, but maybe fixed.

Applied over commit 4a071afbd0.

 > [1] 
https://www.postgresql.org/message-id/CAKJS1f8p-KiEujr12k-oa52JNWWaQUjEjNg%2Bo1MGZk4mHBn_Rg%40mail.gmail.com
[2] 
https://www.postgresql.org/message-id/CAKJS1f8cJOCGyoxi7a_LG7eu%2BWKF9%2BHTff3wp1KKS5gcUg2Qfg%40mail.gmail.com

-- 
regards,
Andrey Lepikhov
Postgres Professional