Re: Performance improvement for joins where outer side is unique

This seems quite a bit better. Having the inner_unique variable as part of the JoinPath struct seems much better than what I had. This seems to remove the requirement of my patch that all joins to that RelOptInfo be unique.

I also quite like the more recent change to make_hashjoin and co. just pass the JoinPath as a parameter.

I don't really like the "(inner unique)" being tagged onto the end of the join node, but there's not much point in spending too much time talking about that right now. There's much better things to talk about. I'm sure we can all bikeshed around that one later.

In joinpath.c you have a restriction to only perform the unique check for inner joins.. This should work ok for left joins too, but it would probably be more efficient to have the left join removal code analyse the SpecialJoinInfos during checks for left join removals. I think it would just be a matter of breaking down the join removal code similar to how I did in my patch, but this time add a bool inner_unique to the SpecialJoinInfo struct. The join_is_legal() function seems to select the correct SpecialJoinInfo if one exists, so add_paths_to_joinrel() shouldn't need to call relation_has_unique_index_for() if it's a LEFT JOIN, as we'll already know if it's unique by just looking at the property.

You've also lost the ability to detect that subqueries are unique:

create table j1(id int primary key);

create table j2(value int not null);

explain select * from j1 inner join (select distinct value from j2) j2 on j1.id=j2.value;

The left join removal code properly detects this, so I think unique joins should too.

I can continue working on your patch if you like? Or are you planning to go further with it?

Regards

David Rowley