Re: Removing INNER JOINs

On 13 January 2015 at 11:29, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 12/3/14 1:08 PM, Tom Lane wrote:
>
>> Heikki Linnakangas<hlinnakangas(at)vmware(dot)com> writes:
>>
>>> >Do you need to plan for every combination, where some joins are removed
>>> >and some are not?
>>>
>> I would vote for just having two plans and one switch node. To exploit
>> any finer grain, we'd have to have infrastructure that would let us figure
>> out*which* constraints pending triggers might indicate transient
>> invalidity of, and that doesn't seem likely to be worth the trouble.
>>
>
> In the interest of keeping the first pass simple... what if there was
> simply a switch node in front of every join that could be removable? That
> means you'd still be stuck with the overall plan you got from not removing
> anything, but simply eliminating the access to the relation(s) might be a
> big win in many cases, and presumably this would be a lot easier to code.

I can't quite get my head around what you mean here, as the idea sounds
quite similar to something that's been discussed already and ruled out.
If we're joining relation a to relation b, say the plan chosen is a merge
join. If we put some special node as the parent of the merge join then how
will we know to skip or not skip any sorts that are there especially for
the merge join, or perhaps the planner choose an index scan as a sorted
path, where now that the join is removed, could become a faster seqscan.
The whole plan switching node discussion came from this exact problem.
Nobody seemed to like the non-optimal plan that was not properly optimised
for having the relation removed.

It also seems that transitioning through needless nodes comes at a cost.
This is why I quite liked the Alternative Plan node idea, as it allowed me
to skip over the alternative plan node at plan initialisation.

Regards

David Rowley