On 30 November 2014 at 23:19, Mart Kelder <mart@kelder31.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.
