Re: Transient plans versus the SPI API

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Anyone have an opinion about that?

> I still have this application where PREPARE takes between 50ms and 300ms
> and EXECUTE 5ms to 10ms, and I can handle 1 PREPARE for 10000 EXECUTE
> quite easily. (Yes the database fits in RAM, and yes when that's no
> longer the case we just upgrade the hardware)

> What does your proposal mean for such a use case?

Well, the policy for when to replan or not remains to be worked out in
detail, but what is likely to happen for such cases is that we'll waste
a few planning cycles before determining that there's no benefit in a
custom plan. So, using the worst-case ends of your ranges above and
assuming that "a few" means "10", we'd go from 300 + 5 * 10000 = 50300
ms to execute the query 10000 times, to 10 * 300 + 5 * 10000 = 53000 ms.
So yes, it'd get a little worse for that use-case. But you have to
weigh that against the likelihood that other use-cases will get better.
If our requirement for a transient-plan mechanism is that no individual
case can ever be worse than before, then we might as well abandon the
entire project right now, because the only way to meet that requirement
is to change nothing.

Of course we could address the worst cases by providing some mechanism
to tell the plancache code "always use a generic plan for this query"
or "always use a custom plan". I'm not entirely thrilled with that,
because it's effectively a planner hint and has got the same problems
as all planner hints, namely that users are likely to get it wrong.
But it would be relatively painless to supply such a hint at the SPI
level, which is why I asked whether we should. It'd be much harder to
do something equivalent at higher levels, which is why I'm not that
eager to do it for SPI.

regards, tom lane