Re: Removing INNER JOINs

On 30 November 2014 at 23:19, Mart Kelder <mart(at)kelder31(dot)nl> wrote:

>
> I think performance can be greatly improved if the planner is able to use
> information based on the current data. I think these patches are just two
> examples of where assumptions during planning are usefull. I think there
> are
> more possibilities for this kind of assumpions (for example unique
> constraints, empty tables).
>
> The problem here is that assumpions done during planning might not hold
> during execution. That is why you placed the final decision about removing
> a
> join in the executor.
>
> If a plan is made, you know under which assumptions are made in the final
> plan. In this case, the assumption is that a foreign key is still valid. In
> general, there are a lot more assumptions, such as the still existing of an
> index or the still existing of columns. There also are soft assumptions,
> assuming that the used statistics are still reasonable.
>
>
Hi Mart,

That's an interesting idea. Though I think it would be much harder to
decide if it's a good idea to go off and replan for things like empty
tables as that's not known at executor startup, and may only be discovered
99% of the way through the plan execution, in that case going off and
replanning and starting execution all over again might throw away too much
hard work.

It does seem like a good idea for things that could be known at executor
start-up, I guess this would likely include LEFT JOIN removals using
deferrable unique indexes... Currently these indexes are ignored by the
current join removal code as they mightn't be unique until the transaction
finishes.

I'm imagining this being implemented by passing the planner a set of flags
which are assumptions that the planner is allowed to make... During the
planner's work, if it generated a plan which required this assumption to be
met, then it could set this flag in the plan somewhere which would force
the executor to check this at executor init. If the executor found any
required flag's conditions to be not met, then the executor would request a
new plan passing all the original flags, minus the ones that the conditions
have been broken on.

I see this is quite a fundamental change to how things currently work and
it could cause planning to take place during the execution of PREPAREd
statements, which might not impress people too much, but it would certainly
fix the weird anomalies that I'm currently facing by trimming the plan at
executor startup. e.g left over Sort nodes after a MergeJoin was removed.

It would be interesting to hear Tom's opinion on this.

Regards

David Rowley