Re: Question on partition pruning involving stable operator: timestamptz_ge_date

Hi,

On Thu, Jan 27, 2022 at 10:28 AM TAO TANG <tang(dot)tao(dot)cn(at)gmail(dot)com> wrote:
> the plan shows all the partitions are pruned, but in gdb tracing, it shows that
> the pruning happens in ExecInitAppend, and during planning stage pg does not
> prune any partitions. this is because in function match_clause_to_partition_key
> do not handle the case for STABLE operator:
>
> if (op_volatile(opno) != PROVOLATILE_IMMUTABLE)
> {
> context->has_mutable_op = true;
>
> /*
> * When pruning in the planner, we cannot prune with mutable
> * operators.
> */
> if (context->target == PARTTARGET_PLANNER)
> return PARTCLAUSE_UNSUPPORTED;
> }
>
> the procs for timestamptz compare with date are STABLE:
>
> proname | provolatile
> ----------------------+-------------
> timestamptz_lt_date | s
> timestamptz_le_date | s
> timestamptz_eq_date | s
> timestamptz_gt_date | s
> timestamptz_ge_date | s
> timestamptz_ne_date | s
> timestamptz_cmp_date | s
> (7 rows)
>
> but in ExecInitAppend call perform_pruning_base_step which do not consider the STABLE
> property of the cmpfn.
>
> so I have serveral questions:
> 1) why in planning the function volatility is considered but not in execInitAppend;

The value of a STABLE expression can change based on runtime
parameters, so while it is guaranteed to remain the same during a
particular execution of a plan in which it is contained, it can change
across multiple executions of that plan (if it is cached, for
example). So the planner cannot assume a particular value of such
expressions when choosing partitions to add to the plan, because each
execution of the plan (each may run in a separate transaction) can
produce different values. ExecInitAppend(), on the other hand, can
assume a particular value when choosing partitions to initialize,
because the value is fixed for a particular execution during which it
runs.

> 2) why timestamptz_xxx_date is STABLE not IMMUTABLE;

Because calculations involving timestamptz values can produce
different results dependkng on volatile settings like timezone,
datestyle, etc.

--
Amit Langote
EDB: http://www.enterprisedb.com