> Well, the issue is that implementing this is a major piece of work. This > post doesn't offer either resources nor a simpler way to do so. There's > no huge debate about the benefit of having a global plan cache, so I'm > not that surprised there's not a huge debate about a post arguing that > we should have one.
Actually, I'm pretty darn skeptical about the value of such a cache for most use-cases. But as long as it can be turned off and doesn't leave residual overhead nor massive added code cruft, I won't stand in the way of someone else investing their time in it.
In any case, as you say, it's moot until somebody steps up to do it.
Well, looking at previous conversations and also at the comment above it doesn't seem like there's a consensus on whether this feature would even be beneficial... The point of my email above was to have that conversation before looking into implementation. Tom, I'm especially interested in understanding why you think this cache wouldn't help most use-cases: I see many applications which don't prepare (i.e. because they use data access layers/O/RMs which don't do it or expose it), and implementing this in the driver seems like the wrong way (although Npgsql and JDBC do it, at least some other languages don't).
In addition, there are also various options/possibilities here and there seems no consensus about that either:
* How should statement plan caching be done for unprepared statements, what strategy should be used? Should a threshold number of unprepared executions be used before PostgreSQL decides to prepare? Should there be a maximum number of autoprepared statements, ejecting the least-recently used one to make room for a new one? Or something else?
* Should the cached plans be shared across connections ("global" cached statements)? Are the savings from global caching greater than the cost of the contention? The savings include both (a) not having to re-prepare the same statement N times on different connections (typically just a one-time application warm-up cost), and (b) not having the memory duplication of N identical statements across statements (a constant cost, not warm-up - but not sure how significant this is). Note that the global/shared discussion is a bit orthogonal to the general autopreparation conversation - the latter has value with or without the former.
Essentially I think it's a good idea to have a conversation about all this before anyone jumps into implementation.