Re: Transient plans versus the SPI API

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> 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.

That is not were I wanted to drift. It's just that I don't have as much
time as I would like to those days, and so it helps me a lot seeing a
worked out example rather than make sure I parse your proposal
correctly. Thanks a lot for your answer, I have a very clear
confirmation on how I read your previous email.

I will have to do some testing, but it could well be that this
application will benefit from locking reductions enough that it buys
this effect back.

> 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.

Yeah.

> 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.

Given the SLA of those prepared queries in my case, I think I could
accept to have to switch from SQL statements to C coded SRF to guarantee
the planning behavior. It will not make the upgrade cheaper, but I
realize it's a very narrow and specific use case.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support