Horton

Re: Removing unneeded self joins

Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>

View thread
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-03-20T12:20: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/14/19 14:21, David Rowley wrote:

> What do you think?


Let's recap the conditions when we can remove a self-join. It is when 
for each outer row, 1) at most one inner row matches the join clauses, 
and 2) it is the same row as the outer one. I'm not sure what (2) means 
precisely in a general case, but for a plain table, we can identify 
these rows by ctid. So when both sides have the same unique index with 
the same clauses, we conclude that we are always dealing with the same 
row (as identified by ctid) on both sides, hence the join can be 
replaced with a scan.

The code I wrote just checks for the above conditions. The data we need 
for these checks is a byproduct of checking the relations for 
uniqueness, which we do anyway, so we just cache it for a negligible cost.

I didn't write it in a more generic way because I don't understand the 
conditions for generic case. In your DISTINCT example, the join can be 
removed indeed. But if we select some columns from the inner side apart 
from the join ones, we can't remove the join anymore:

select * from t1, (select distinct on (a) a, b from t1) tt where t1.a = 
tt.a;

I think this might be a different kind of optimization, where we remove 
the self-join if the inner side is unique, and no inner columns are 
selected besides the join ones.


Also, reading your letter I realized that I don't commute the index 
clauses correctly before comparing them in is_unique_self_join, so I 
fixed this in the new version of the patch.


-- 
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company