On Wed, Jan 13, 2016 at 10:47 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Vladimir Sitnikov <sitnikov.vladi...@gmail.com> writes: >> Note: I state that mixing "kinds" of bind values is a bad application >> design anyway. In other words, application developer should understand >> if a query is DWH-like (requires replans) or OLTP-like (does not >> require replans). Agreed? > > No, not agreed. As was already pointed out upthread, such information > is not available in many use-cases for the plancache. > > The real problem here IMO is inaccurate plan cost estimates, and that's > not something that there is any easy fix for.
Not really. Even if the cost estimates for all of the plans tried are perfectly accurate, you'll have only seen 5 values when you decide to switch to a generic plan. If the 6th, 60th, 600th, or 6000th execution uses a parameter where a custom plan would have been a big win, you will blindly use the generic plan anyway and lose bigtime. On the other hand, if first five plans are all equivalent to each other and to the generic plan, then you've spent the cost of uselessly replanning six times instead of just caching the first plan and being done with it. I'm aware of an actual case where that extra re-planning causes a serious performance problem, aggregated across many queries and many backends. This isn't the first complaint about this mechanism that we've gotten, and it won't be the last. Way too many of our users are way more aware than they should be that the threshold here is five rather than any other number, which to me is a clear-cut sign that this needs to be improved. How to improve it is a harder question. We lack the ability to do any kind of sensitivity analysis on a plan, so we can't know whether there are other parameter values that would have resulted in a different plan, nor can we test whether a particular set of parameter values would have changed the outcome. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers