Re: Removing unneeded self joins

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