Now, in my experience, the current system for custom plans vs. generic
plans doesn't approach the problem in this way at all, and in my
experience that results in some pretty terrible behavior. It will do
things like form a custom plan every time because the estimated cost
of the custom plan is lower than the estimated cost of the generic
plan even though the two plans are structurally identical; only the
estimates differ. It will waste gobs of CPU cycles by replanning a
primary key lookup 5 times just on the off chance that a lookup on the
primary key index isn't the best option. But this patch isn't going
to fix any of that. The best we can probably do is try to adjust the
costing for Append paths in some way that reflects the costs and
benefits of pruning. I'm tentatively in favor of trying to do
something modest in that area, but I don't have a detailed proposal.
I just realized this issue recently and reported it at [1], then Amit pointed
me to this issue being discussed here, so I would like to continue this topic
here.
I think we can split the issue into 2 issues. One is the partition prune in initial
partition prune, which maybe happen in custom plan case only and caused
the above issue. The other one happens in the "Run-Time" partition prune,
I admit that is an important issue to resolve as well, but looks harder. So I
think we can fix the first one at first.
The proposal to fix the first one is we can remember how many partitions
survived after plan time pruned for a RelOptInfo for a custom plan. and record
such information in the CachedPlanSource. When we count for the cost of a
generic plan, we can reduce the cost based on such information.
Any thought about this? I'd be sorry if I missed some already existing discussion
on this topic.