Horton

Re: Removing unneeded self joins

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alena Rybakina <lena.ribackina@yandex.ru>
Cc: "Gregory Stark (as CFM)" <stark.cfm@gmail.com>, Michał Kłeczek <michal@kleczek.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Alexander Korotkov <aekorotkov@gmail.com>
Date: 2023-10-11T03:51:26Z
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 11/10/2023 02:29, Alena Rybakina wrote:
> I have reviewed your patch and I noticed a few things.

Thanks for your efforts,

> I have looked at the latest version of the code, I assume that the error 
> lies in the replace_varno_walker function, especially in the place where 
> we check the node by type Var, and does not form any NullTest node.

It's not a bug, it's an optimization we discussed with Alexander above.

> Secondly, I added some code in some places to catch erroneous cases and 
> added a condition when we should not try to apply the self-join-removal 
> transformation due to the absence of an empty self-join list after 
> searching for it and in general if there are no joins in the query. 
> Besides, I added a query for testing and wrote about it above. I have 
> attached my diff file.
Ok, I will look at this
> In addition, I found a comment for myself that was not clear to me. I 
> would be glad if you could explain it to me.
> 
> You mentioned superior outer join in the comment, unfortunately, I 
> didn't find anything about it in the PostgreSQL code, and this 
> explanation remained unclear to me. Could you explain in more detail 
> what you meant?
I meant here that only clauses pushed by reconsider_outer_join_clauses() 
can be found in the joininfo list, and they are not relevant, as you can 
understand.
Having written that, I realized that it was a false statement. ;) - 
joininfo can also contain results of previous SJE iterations, look:

CREATE TABLE test (oid int PRIMARY KEY);
CREATE UNIQUE INDEX ON test((oid*oid));
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c1.oid=c2.oid AND c1.oid*c2.oid=c3.oid*c3.oid;
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c1.oid=c3.oid AND c1.oid*c3.oid=c2.oid*c2.oid;
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c3.oid=c2.oid AND c3.oid*c2.oid=c1.oid*c1.oid;

Having executed this SQL code, you could see that in the last query, the 
SJE feature didn't delete one of the JOINs because of the reason I had 
written above.
It's not an one-minute fix - I will try to propose solution a bit later.

-- 
regards,
Andrey Lepikhov
Postgres Professional