Re: Removing INNER JOINs - Mailing list pgsql-hackers

On Wed, Dec 3, 2014 at 4:29 AM, David Rowley <dgrowleyml@gmail.com> wrote:
> *** Method 1: Removing Inner Joins at planning time:
>
> *** Method 2: Marking scans as possibly skippable during planning, and
> skipping joins at execution (Andres' method)
>
> *** Method 3: Marking scans as possibly skippable during planning and
> removing redundant join nodes at executor startup (Simon's method)
[....]
> a. can we invoke the planner during executor init?

I'm pretty sure that we can't safely invoke the planner during
executor startup, and that doing surgery on the plan tree (option #3)
is unsafe also.  I'm pretty clear why the latter is unsafe: it might
be a copy of a data structure that's going to be reused.  I am less
clear on the specifics of why the former is unsafe, but what I think
it boils down to is that the plan per se needs to be finalized before
we begin execution; any replanning needs to be handled in the
plancache code.  I am not sure whether it's feasible to do something
about this at the plancache layer; we have an is_oneshot flag there,
so perhaps one-shot plans could simply test whether there are pending
triggers, and non-oneshot plans could forego the optimization until we
come up with something better.

If that doesn't work for some reason, then I think we basically have
to give up on the idea of replanning if the situation becomes unsafe
between planning and execution.  That leaves us with two alternatives.
One is to create a plan incorporating the optimization and another not
incorporating the optimization and decide between them at runtime,
which sounds expensive.  The second is to create a plan that
contemplates performing the join and skip the join if it turns out to
be possible, living with the fact that the resulting plan might be
less than optimal - in other words, option #2.  I am not sure that's
all that bad.  Planning is ALWAYS an exercise in predicting the
future: we use statistics gathered at some point in the past, which
are furthermore imprecise, to predict what will happen if we try to
execute a given plan at some point in the future.  Sometimes we are
wrong, but that doesn't prevent us from trying to our best to predict
the outcome; so here.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company