Andy Fan <zhihui.fan1213@gmail.com> writes: > On Mon, Sep 28, 2020 at 4:46 AM David Rowley <dgrowleyml@gmail.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?
