Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 7:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> > On Mon, Sep 28, 2020 at 4:46 AM David Rowley <dgrowleyml(at)gmail(dot)com>
> wrote:
> >> Thanks for showing an interest in partition pruning. Unfortunately,
> >> it's not possible to use stable functions to prune partitions during
> >> planning.
>
> > 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.
>
> No, that's still wrong. The contract for a stable function is that
> its result won't change over execution of a single query; but that
> says *execution*, not *planning and execution*.
>
> In particular, the canonical example of a stable function is one
> whose result depends on a database query. The reason it can be
> considered stable is that within a single outer query, the MVCC
> snapshot it's used with won't change. But we take a new snapshot
> (later than the planner's snapshot) when beginning execution.
>
> Somebody (Robert Haas, if memory serves, which it might not)
> tried to change that a few years ago. It blew up pretty well,
> and was eventually reverted, because of undesirable side-effects
> on user-visible query semantics. You'd have to check the archives
> for details.
>
> It's possible that we could make that work differently in serializable
> mode, thanks to the longer persistence of snapshots. Not sure that
> it'd be desirable for planning to work differently in serializable
> mode, though.
>
> regards, tom lane
>

Well, that's very interesting. Specific to my user case,
SELECT * FROM p WHERE pkey = to_date('2018-12-13', 'yyyy-mm-dd)';
p has 1500+ partitions and planning takes lots of time, which is so same
with SELECT * FROM p WHERE pkey = '2018-12-13', however the planning
time difference is so huge, that doesn't make sense in human view. Can
we do something for that? to_date(text, text) should be a "immutable"
function
IMO. Does that have a semantic issue or other issues?

--
Best Regards
Andy Fan