I was looking into using prepared statements and using the auto plan_cache_mode. The issue is that the current sample of data collected to determine whether to use custom plans or the generic one is very small and susceptible to a bad set of queries that might pick the suboptimal choice. It’s currently hard coded as 5 custom plans [https://github.com/postgres/postgres/blob/master/src/backend/utils/cache/plancache.c#L1051], but I’d like to see this as a configurable parameter if possible. Failing that - I’d love to hear any recommendations on who to deal with this?
I'd want PG to be accurate at picking between the custom_plan and generic_plan strategy, so that we could safely realize the benefits that cached plans can give us.
I’d also be curious to know people’s experience with using this and if moving to using prepared statements has resulted in a latency regression due to a bad strategy being used in production. I’m a contributor to the PgCat [https://github.com/postgresml/pgcat] project and recently added support for prepared statements so I’m looking to understand the space a little better as we would be looking to migrate services that were previously not using prepared statements to using them.
