> > I am an author of one of the proposal (autoprepare which is in commit fest > now), but I think that sooner or later Postgres has to come to solution > with shared DB caches/prepared plans. > Please correct me if I am wrong, but it seems to me that most of all other > top DBMSes having something like this. > Such decision can provide a lot of different advantages: > 1. Better memory utilization: no need to store the same data N times where > N is number of backends and spend time for warming cache. > 2. Optimizer can spend more time choosing better plan which then can be > used by all clients. Even now time of compilation of some queries several > times exceeds time of their execution. > 3. It is simpler to add facilities for query plan tuning and maintaining > (storing, comparing,...) > 4. It make is possible to control size of memory used by caches. Right now > catalog cache for DB with hundred thousands and tables and indexes > multiplied by hundreds of backends can consume terabytes of memory. > 5. Shared caches can simplify invalidation mechanism. > 6. Almost all enterprise systems working with Postgres has to use some > kind of connection pooling (pgbouncer, pgpool,...). It almost exclude > possibility to use prepared statements. Which can slow down performance up > to two times. >
Just wanted to say I didn't see this email before my previous response, but I agree with all of the above. The last point is particularly important, especially for short-lived connection scenarios, the most typical of which is web. > There is just one (but very important) problem which needs to be solved: > access to shared cache should be synchronized. > But there are a lot of other shared resources in Postgres (procarray, > shared buffers,...). So I do not think that it is unsolvable problem and > that it can cause degrade of performance. > > So it seems to be obvious that shared caches/plans can provide a lot of > advantages. But it is still not clear to me the value of this advantages > for real customers. > Using -M prepared protocol in pgbench workload can improve speed up to > two times. But I have asked real Postgres users in Avito, Yandex, MyOffice > and them told me > that on their workloads advantage of prepared statements is about 10%. 10% > performance improvement is definitely not a good compensation for rewriting > substantial part of Postgres core... > Just wanted to say that I've seen more than 10% improvement in some real-world application when preparation was done properly. Also, I'm assuming that implementing this wouldn't involve "rewriting substantial part of Postgres core", and that even 10% is quite a big gain, especially if it's a transparent/free one as far as the user is concerned (no application changes).
