Justin Blank <justin.blank@xxxxxxxxx> writes: > My idea had been that even if the custom plans average higher cost > than the generic plan, it is still worth considering custom plans. If > 1 time in 5, the custom plan is 10% of the cost of the generic plan, > it doesn't matter if the average custom plan is worse, you should at > least determine the cost of the custom plan before deciding whether to > use it or the generic plan. Even if 80% of the time, you don't use the > custom plan, the payoff in the good case may be worth it. What you'd have to argue is that the average cost of the custom plans (including the planning time) is less than the average cost of the generic plan (including planning time to make a custom plan that's then not used). I'm not exactly convinced that that's any better than "always use a custom plan" mode: once you've gone to the trouble of making a custom plan, why not use it? I'd be the first to agree that this logic is squishy as heck, and I'd love to find a better way. There are a couple of very nasty problems to think about, though: * The estimate of the cost of planning is pretty laughable. It's not a very detailed estimate, and even if it were there's a scale problem in comparing it to estimated execution costs. So I'm hesitant to put any great weight on that estimate. * Even comparing the estimated costs of custom and generic plans is full of pitfalls, because they are based on different estimates of the selectivity of WHERE clauses. We've had reports for instance of the cache picking a generic plan that was awful for the actual parameter values, because the generic plan's cost was based on an assumed-average case, while the custom plans' cost reflected reality more accurately and hence were higher than the generic estimate even though in reality they were the better choice. (I don't recall precise details, but I think it was something like the actual parameter matching a very common value so that the number of rows involved would be much greater than the generic plan assumed.) Without some way to tame those problems, I'm not sure that marginal tweaks in the plan-selection rule will accomplish a lot. One idea perhaps could be to pay attention to how much the custom plans' estimated rowcounts move around, and only switch to a generic plan if there's not too much change and the generic plan's estimated rowcounts are in the same range. Or in other words, rather than believing a cost comparison, what we'd look for is whether we get basically the same plan in generic mode as in custom mode. I'm not sure how to mechanize that, though. regards, tom lane
