Re: Allowing join removals for more join types

On Mon, May 19, 2014 at 5:47 PM, Dilip kumar <dilip(dot)kumar(at)huawei(dot)com> wrote:

>
>
> So I think now when you are considering this join removal for subqueries
> then this can consider other case also like unique index inside subquery,
>
> because in attached patch unique index is considered only if its
> RTE_RELATION
>
>
>
> + if (innerrel->rtekind == RTE_RELATION &&
>
> + relation_has_unique_index_for(root, innerrel,
> clause_list, NIL, NIL))
>
> return true;
>
>
>
>
>

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);
RelOptInfo *subqueryrel = find_base_rel(innerrel->subroot, subqueryrelid);
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?

Regards

David Rowley