I'm currently in the early stages of looking into expanding join removals.
As I said above, I'm in the early stages of looking at this and I'm currently a bit confused. Basically I've put a breakpoint at the top of the join_is_removable function and I can see that innerrel->rtekind is RTE_SUBQUERY for my query, so the function is going to return false. So what I need to so is get access to innerrel->subroot->parse so that I can look at groupClause and distinctClause. The thing is innerrel->subroot is NULL in this case, but I see a comment for subroot saying "subroot - PlannerInfo for subquery (NULL if it's not a subquery)" so I guess this does not also mean "subroot - PlannerInfo for subquery (NOT NULL if it's a subquery)"?
Has anyone got any pointers to where I might be able to get the Query details for the subquery? These structures are quite new to me.
I think I've managed to answer my own question here. But please someone correct me if this sounds wrong.
It looks like the existing join removals are done quite early in the planning and redundant joins are removed before any subqueries from that query are planned. So this innerrel->subroot->parse has not been done yet. It seems to be done later in query_planner() when make_one_rel() is called.
The best I can come up with on how to implement this is to have 2 stages of join removals. Stage 1 would be the existing stage that attempts to remove joins from non subqueries. Stage 2 would happen just after make_one_rel() is called from query_planner(), this would be to attempt to remove any subqueries that are not need, and if it managed to remove any it would force a 2nd call to make_one_rel().
Does this sound reasonable or does it sound like complete non-sense?