Re: [HACKERS] Runtime Partition Pruning

>
>
>
> 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