On Mon, 28 Sep 2020 at 08:59, Andy Fan <zhihui.fan1213@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