I've been looking into the way that postgres decides whether to use a custom or generic plan. I believe I understand how, but I have confused myself about the why. In plancache.c, the code always executes a custom plan for the first five iterations, and afterwards, it compares the average cost of the custom plan (including the planning cost) to the cost of a generic plan (not including planning), and choose the option with the lower cost. 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. However, after starting to implement that idea, I became confused, and the underlying logic of comparing custom and generic plans stopped making sense to me. I can think of two different reasons the cost estimates for the generic plan and custom plan can differ: 1. The two plans substantively differ: they use a different join order, different join strategy, or different indexes. 2. They do not fundamentally differ, but the custom plan makes different estimates of the selectivity of predicates because it knows their precise values. For the sake of deciding between custom and generic plans, it seems like only the first type of difference matters. So my first question is whether there's any value in comparing the cost estimates for custom plans that have the same access plan as the generic plan. Continuing to cases where the plans differ meaningfully, I'm not sure that comparison makes sense either. If the custom plan beats the generic plan, it seems reasonable to take that as a real improvement. However, if the custom plan has a higher cost than the generic plan, this may or may not count against the custom plan. The planner used the bind parameters of the specific query. So it may be that the custom plan really is worse (the planner has made a mistake) but it may also be that it considered the access plan of the generic plan, and it was worse for those particular bind parameters. In this case, the custom plan appears worse than the generic plan, but would ideally be preferred. Does my reasoning make sense? Or have I misunderstood something about this process and how to reason about these cost estimates? I'm very unsure, and worrying I've completely misunderstood, as if I'm right, the logic in plancache.c doesn't make much sense. Justin Blank
