Re: Partition prune with stable Expr

Thank you David for coming:)

On Mon, Sep 28, 2020 at 4:46 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Mon, 28 Sep 2020 at 08:59, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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