Postgres wont remove useless joins, when the UNIQUE index is partial
remove_useless_join does not prove uniqueness if the unique index is partial, and therefore wont remove the join if no columns are referenced (see example in bottom).
I have been trying to look around the source code and from what I have identified the problem seems to be that "check_index_predicates(..)" happens after "remove_useless_join(..)", and therefore cannot see that the unique index is actually covered by the join
From analyzejoins.c:612, rel_supports_distinctness(..)
if (ind->unique && ind->immediate &&
(ind->indpred == NIL || ind->predOK))
But the problem is ind->predOK is calculated in check_index_predicates(..) but this happens later so ind->predOK is always false when checked here.
I have tried to add check_index_predicates(..) to rel_supports_distinctness(..) and this produces the expected plan, but I have no idea of the implication of doing check_index_predicates(..) earlier.
This is my first time looking at the postgres source code, so I know attached "patch" is not the solution, but any pointers on where to go from here would be appreciated.
CREATE TABLE a (
id INTEGER PRIMARY KEY,
sub_id INTEGER NOT NULL,
CREATE UNIQUE INDEX ON a (sub_id) WHERE (deleted_at IS NULL);
EXPLAIN SELECT 1 FROM a AS a LEFT JOIN a AS b ON a.id = b.sub_id AND b.deleted_at IS NULL;