I've just had a bit of a look at implementing checks allowing subqueries with unique indexes on the join cols being removed, but I'm hitting a bit of a problem and I'm not quite sure if this is possible at this stage of planning.
In the function join_is_removable() the variable innerrel is set to the RelOptInfo of the relation which we're checking if we can remove. In the case of removing subqueries the innerrel->rtekind will be RTE_SUBQUERY. I started going over the pre-conditions that the sub query will need to meet for this to be possible and the list so far looks something like:
1. Only a single base table referenced in the sub query.
2. No FOR UPDATE clause
3. No GROUP BY or DISTINCT clause
4. No set returning functions
5. no volatile functions.
6. has unique index that covers the join conditions or a subset of.
I'm hitting a bit of a roadblock on point 1. Here's a snipped from my latest attempt:
if (bms_membership(innerrel->relids) == BMS_SINGLETON)
{
int subqueryrelid = bms_singleton_member(innerrel->relids);
if (relation_has_unique_index_for(root, subqueryrel, clause_list, NIL, NIL))
return true;
}
But it seems that innerrel->subroot is still NULL at this stage of planning and from what I can tell does not exist anywhere else yet and is not generated until make_one_rel() is called from query_planner()
Am I missing something major here,or does this sound about right?
