Thank you David for coming:) On Mon, Sep 28, 2020 at 4:46 AM David Rowley <dgrowle...@gmail.com> wrote:
> On Mon, 28 Sep 2020 at 08:59, Andy Fan <zhihui.fan1...@gmail.com> wrote: > > I find we can't prune partitions in the planner if the qual is a stable > function. > > > IMO, we should do it. Why not? > > Thanks for showing an interest in partition pruning. Unfortunately, > it's not possible to use stable functions to prune partitions during > planning. > > NOW() is one example of a function that's stable, but the return value > will change over time. If we used the return value of that to perform > partition pruning then we'd end up with a plan that's wrong over time. > > Sigh.. I understand you now, I ignored the plan can be cached for later use. Without that, we should be able to prune with stable function. I know the run-time partition prune can help with this, but it can't help with planning time. I run into some cases that SELECT * FROM p WHERE pkey = to_date(..); p has 1500+ partitions and planning takes lots of time. and users are not willing to remove the to_date('2018-11-11', 'yyyy-mm-dd') style code since too much and can't find out all of them at once. Actually I think to_date should be marked as immuable rather than stable. -- Best Regards Andy Fan