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*.
I have a slightly different opinion about the impact of "cached the plan
for later use will be wrong" now. Generic plan will never be partition
pruned plan since we don't know which partition to prune at plan time.
So for any cached plan, it is not a plan time partition pruned plan.
Partition prune with stable expr is still unacceptable even this is not
an issue but hope the snapshot issue will be the only one issue to
fix in future for this direction. I'd like to know if I am wrong again.
Indeed I was wrong again. I'd like to end this thread with this
understanding fix.
prepare s as select * from measurement where logdate = $1 ;
execute s(now());
In this case, even if we run the planning time partition prune with a stable
function, we can still get the correct result (ignore the different snapshot case).
since the generic plan includes all the partitions and just do initial partition
prune case.
However if we create the prepared stmt like prepare s as select * from
measurement where logdate = now(); Then the cached plan should be
wrong. Actually this example is exactly the same as Daivd's example
at the beginning..