> > > > 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. [1] https://www.postgresql.org/message-id/CA%2BHiwqGsP2L0BW1ad58HRSj1NouNSVHLfL5pm7%3DPBTvL0b%2B-BQ%40mail.gmail.com -- Best Regards Andy Fan
