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. However ... one specific aspect of that is that to some extent, the cost estimate made for the generic plan is incommensurate with the estimates for the custom plans because the latter are made with more information. I don't remember the details of your specific case anymore, but we've seen cases where the generic plan is falsely estimated to be cheaper than custom plans because of this. I wonder whether it would be useful to reject a generic plan anytime its estimate is less than the average (or minimum?) estimate for the custom plans. If it is less, then either (1) the generic plan is falsely optimistic, or (2) the specific parameter values provided for the custom plans were all ones for which the planner could see that the generic plan was non-optimal. If (2) holds for the first few custom plans then it's not unreasonable to suppose that it will keep on holding, and we had better not use the generic plan. Basically, the case we're *expecting* to see is that a custom plan is the same or better cost as the generic plan --- same cost if it's really the same plan, better cost if knowing the parameter values allows some optimization to be performed (LIKE-pattern-to-index conversion, partition scan suppression via constraint exclusion, etc). If we get a higher cost estimate for the custom plan then something is fishy and we shouldn't believe it. Maybe I'm missing some case where that situation would arise naturally. Or maybe such a rule wouldn't actually help in very many real-world cases. But it seems worth looking into. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers