On 15.06.2023 12:04 PM, Hannu Krosing wrote:
So a fair bit of work but also a clearly defined benefits of 1) reduced memory usage 2) no need to rebuild caches for each new connection 3) no need to track PREPARE statements inside connection poolers.
Shared plan cache (not only prepared statements cache) also opens way to more sophisticated query optimizations. Right now we are not performing some optimization (like constant expression folding) just because them increase time of processing normal queries. This is why queries generated by ORMs or wizards, which can contain a lot of dumb stuff, are not well simplified by Postgres. With MS-Sql it is quite frequent that query execution time is much smaller than query optimization time. Having shared plan cache allows us to spend more time in optimization without risk to degrade performance.
