Re: Removing INNER JOINs

On Wed, Dec 3, 2014 at 5:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 3 December 2014 at 09:29, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > *** Method 3: Marking scans as possibly skippable during planning and
> > removing redundant join nodes at executor startup (Simon's method)
> >
> > Pros:
> > 1. The plan can be executed as normal if there are any foreign key
> triggers
> > pending.
> > 2. Does not require extra code in all join types (see cons #2 above)
> > 3. Does not suffer from extra node visiting overhead (see cons #3 above)
> >
> > Cons:
> > 1. Executor must modify the plan.
> > 2. Planner may have generated a plan which is not optimal for
> modification
> > by the executor (e.g. Sort nodes for merge join, or index scans for
> > pre-sorted input won't become seqscans which may be more efficient as
> > ordering may not be required after removing a merge join)
> >
> > With each of the methods listed above, someone has had a problem with,
> and
> > from the feedback given I've made changes based and ended up with the
> next
> > revision of the patch.
> >
> > Tom has now pointed out that he does not like the executor modifying the
> > plan, which I agree with to an extent as it I really do hate the extra
> > useless nodes that I'm unable to remove from the plan.
>
> I guess we need an Option node. Tom and I discussed that about an aeon ago.
>
> The Option node has a plan for each situation. At execution time, we
> make the test specified in the plan and then select the appropriate
> subplan.
>
> That way we can see what is happening in the plan and the executor
> doesn't need to edit anything.
>

So the planner keeps all possibility satisfying plans, or it looks at the
possible conditions (like presence of foreign key for this case, for eg)
and then lets executor choose between them?

So is the idea essentially making the planner return a set of "best" plans,
one for each condition? Are we assured of their optimality at the local
level i.e. at each possibility?

IMO this sounds like punting the planner's task to executor. Not to mention
some overhead for maintaining various plans that might have been discarded
early in the planning and path cost evaluation phase (consider a path with
pathkeys specified, like with ORDINALITY. Can there be edge cases where we
might end up invalidating the entire path if we let executor modify it, or,
maybe just lose the ordinality optimization?)

I agree that executor should not modify plans, but letting executor choose
the plan to execute (out of a set from planner, of course) rather than
planner giving executor a single plan and executor not caring about the
semantics, seems a bit counterintuitive to me. It might be just me though.

Regards,

Atri

--
Regards,

Atri
*l'apprenant*