On Fri, May 12, 2017 at 10:50:41AM +0300, Konstantin Knizhnik wrote: > Definitely changing session context (search_path, date/time format, ...) may > cause incorrect behavior of cached statements.
I wonder if we should clear the cache whenever any SET command is issued. > Actually you may get the same problem with explicitly prepared statements > (certainly, in the last case, you better understand what going on and it is > your choice whether to use or not to use prepared statement). > > The fact of failure of 7 regression tests means that autoprepare can really > change behavior of existed program. This is why my suggestion is to switch > off this feature by default. I would like to see us target something that can be enabled by default. Even if it only improves performance by 5%, it would be better overall than a feature that improves performance by 90% but is only used by 1% of our users. > But in 99.9% real cases (my estimation plucked out of thin air:) there will > be no such problems with autoprepare. And it can significantly improve > performance of OLTP applications > which are not able to use prepared statements (because of working through > pgbouncer or any other reasons). Right, but we can't ship something that is 99.9% accurate when the inaccuracy is indeterminate. The bottom line is that Postgres has a very high bar, and I realize you are just prototyping at this point, but the final product is going to have to address all the intricacies of the issue for it to be added. > Can autoprepare slow down the system? > Yes, it can. It can happen if application perform larger number of unique > queries and autoprepare cache size is not limited. > In this case large (and infinitely growing) number of stored plans can > consume a lot of memory and, what is even worse, slowdown cache lookup. > This is why I by default limit number of cached statements > (autoprepare_limit parameter) by 100. Yes, good idea. > I am almost sure that there will be some other issues with autoprepare which > I have not encountered yet (because I mostly tested it on pgbench and > Postgres regression tests). > But I am also sure that benefit of doubling system performance is good > motivation to continue work in this direction. Right, you are still testing to see where the edges are. > My main concern is whether to continue to improve current approach with > local (per-backend) cache of prepared statements. > Or create shared cache (as in Oracle). It is much more difficult to > implement shared cache (the same problem with session context, different > catalog snapshots, cache invalidation,...) > but it also provides more opportunities for queries optimization and tuning. I would continue in the per-backend cache direction at this point because we don't even have that solved yet. The global cache is going to be even more complex. Ultimately I think we are going to want global and local caches because the plans of the local cache are much more likely to be accurate. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
