Обсуждение: Question on partition pruning involving stable operator: timestamptz_ge_date
Hi,
I tested the following case in PostgreSQL master:58e2e6
the partition table created:
create table tbl_dts (dts timestamp with time zone not null) partition by range(dts);
create table tbl_dts_1 partition of tbl_dts for values from ('2021-07-02') to ('2021-08-01');
create table tbl_dts_2 partition of tbl_dts for values from ('2021-08-02') to ('2021-09-01');
create table tbl_dts_3 partition of tbl_dts for values from ('2021-09-02') to ('2021-10-01');
create table tbl_dts_4 partition of tbl_dts for values from ('2021-10-02') to ('2021-11-01');
create table tbl_dts_1 partition of tbl_dts for values from ('2021-07-02') to ('2021-08-01');
create table tbl_dts_2 partition of tbl_dts for values from ('2021-08-02') to ('2021-09-01');
create table tbl_dts_3 partition of tbl_dts for values from ('2021-09-02') to ('2021-10-01');
create table tbl_dts_4 partition of tbl_dts for values from ('2021-10-02') to ('2021-11-01');
and the query:
explain select * from tbl_dts where dts between '2022-01-20'::date and '2022-01-26'::date;
QUERY PLAN
---------------------------------------------
Append (cost=0.00..175.82 rows=44 width=8)
Subplans Removed: 4
(2 rows)
---------------------------------------------
Append (cost=0.00..175.82 rows=44 width=8)
Subplans Removed: 4
(2 rows)
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;
}
{
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)
----------------------+-------------
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;
2) why timestamptz_xxx_date is STABLE not IMMUTABLE;
thanks.
Re: Question on partition pruning involving stable operator: timestamptz_ge_date
От
Amit Langote
Дата:
Hi,
On Thu, Jan 27, 2022 at 10:28 AM TAO TANG <tang.tao.cn@gmail.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