Horton

Re: Removing unneeded self joins

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, Andrei Lepikhov <a.lepikhov@postgrespro.ru>, Alexander Korotkov <aekorotkov@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>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-02T00:25:00Z
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 Mon, Jun 17, 2024 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
>
> > Another thing that made me pretty sad was 8c441c082 (Forbid SJE with
> > result relation).  While I don't claim that that destroyed the entire
> > use case for SJE, it certainly knocked its usefulness down by many
> > notches, maybe even to the point where it's not worth putting in the
> > effort needed to get it to re-committability.  So I think we need to
> > look harder at finding a way around that.  Is the concern that
> > RETURNING should return either old or new values depending on which
> > RTE is mentioned?  If so, maybe the feature Dean has proposed to
> > allow RETURNING to access old values [1] is a prerequisite to moving
> > forward.  Alternatively, perhaps it'd be good enough to forbid SJE
> > only when the non-target relation is actually mentioned in RETURNING.
> >
> >                         regards, tom lane
> >
> > [1] https://www.postgresql.org/message-id/flat/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

attached patchset:
v3-0001-Remove-useless-self-joins.patch
v3-0002-make-SJE-to-apply-DML-MERGE-UPDATE-INSERT-DELETE.patch
v3-0003-use-SJE-conditionally-with-delete-update-merge-RE.patch

v3-0001: based on [1] patch, replace all replace_varno to ChangeVarNodes.
v3-0002: make SJE apply to UPDATE/DELETE/MERGE
v3-0003: make SJE apply to UPDATE/DELETE/MERGE RETURNING conditionally
v3-0001, v3-0002 content, reasoning is the same as the previous thread[2].
v3-0003 is new.
to make sure it's correct, I have added a lot of tests,
Some of this may be contrived, maybe some of the tests are redundant.

[1]: https://postgr.es/m/55f680bc-756d-4dd3-ab27-3c6e663b0e4c%40postgrespro.ru
[2]: https://postgr.es/m/CACJufxG3sqJKe1OskHhn7OCdtrEeeRFcD8R4TTQE%2BLGJEQaL9w%40mail.gmail.com