MIME-Version: 1.0
From: TAO TANG <tang.tao.cn@gmail.com>
Date: Thu, 27 Jan 2022 09:28:32 +0800
Message-ID: <CAM7-PtGpTMG2wusALf33a0-jZgSEy5AKYoL1WOa3KFrSOENtHQ@mail.gmail.com>
Subject: Question on partition pruning involving stable operator: timestamptz_ge_date
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003423f305d6863d44"
Archived-At: <https://www.postgresql.org/message-id/CAM7-PtGpTMG2wusALf33a0-jZgSEy5AKYoL1WOa3KFrSOENtHQ%40mail.gmail.com>
Precedence: bulk

--0000000000003423f305d6863d44
Content-Type: text/plain; charset="UTF-8"

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');

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)

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;
2) why timestamptz_xxx_date is STABLE not IMMUTABLE;

thanks.

--0000000000003423f305d6863d44
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>I tested the following c=
ase in PostgreSQL master:58e2e6</div><div><br></div><div><div>the partition=
 table created:<br></div><div><br></div><div><div>=C2=A0=C2=A0=C2=A0 create=
 table tbl_dts (dts timestamp with time zone not null) partition by range(d=
ts);<br>=C2=A0=C2=A0=C2=A0 create table tbl_dts_1 partition of tbl_dts for =
values from (&#39;2021-07-02&#39;) to (&#39;2021-08-01&#39;);<br>=C2=A0=C2=
=A0=C2=A0 create table tbl_dts_2 partition of tbl_dts for values from (&#39=
;2021-08-02&#39;) to (&#39;2021-09-01&#39;);<br>=C2=A0=C2=A0=C2=A0 create t=
able tbl_dts_3 partition of tbl_dts for values from (&#39;2021-09-02&#39;) =
to (&#39;2021-10-01&#39;);<br>=C2=A0=C2=A0=C2=A0 create table tbl_dts_4 par=
tition of tbl_dts for values from (&#39;2021-10-02&#39;) to (&#39;2021-11-0=
1&#39;);</div><div><br></div><div>and the query:<br></div></div><div><div><=
br></div><div>=C2=A0=C2=A0=C2=A0 explain select * from tbl_dts where dts be=
tween &#39;2022-01-20&#39;::date and &#39;2022-01-26&#39;::date;</div><div>=
<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=
=A0 QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0<br>---------------------------------------------<br>=C2=A0Append =C2=A0=
(cost=3D0.00..175.82 rows=3D44 width=3D8)<br>=C2=A0 =C2=A0Subplans Removed:=
 4<br>(2 rows)</div><div><br></div><div>the plan shows all the partitions a=
re pruned, but in gdb tracing, it shows that <br></div><div>the pruning hap=
pens in ExecInitAppend, and during planning stage pg does not</div><div>pru=
ne any partitions. this is because in function match_clause_to_partition_ke=
y<br></div><div>do not handle the case for STABLE operator:</div><div><br><=
/div><div>if (op_volatile(opno) !=3D PROVOLATILE_IMMUTABLE)<br>		{<br>			co=
ntext-&gt;has_mutable_op =3D true;<br><br>			/*<br>			 * When pruning in th=
e planner, we cannot prune with mutable<br>			 * operators.<br>			 */<br>		=
	if (context-&gt;target =3D=3D PARTTARGET_PLANNER)<br>				return PARTCLAUSE=
_UNSUPPORTED;<br>		}</div><div><br></div><div>the procs for timestamptz com=
pare with date are STABLE:</div><div><br></div><div>=C2=A0 =C2=A0 =C2=A0=C2=
=A0 proname =C2=A0 =C2=A0 =C2=A0 =C2=A0| provolatile <br>------------------=
----+-------------<br>=C2=A0timestamptz_lt_date =C2=A0| s<br>=C2=A0timestam=
ptz_le_date =C2=A0| s<br>=C2=A0timestamptz_eq_date =C2=A0| s<br>=C2=A0times=
tamptz_gt_date =C2=A0| s<br>=C2=A0timestamptz_ge_date =C2=A0| s<br>=C2=A0ti=
mestamptz_ne_date =C2=A0| s<br>=C2=A0timestamptz_cmp_date | s<br>(7 rows)</=
div><div><br></div><div>but in ExecInitAppend call perform_pruning_base_ste=
p which do not consider the STABLE</div><div>property of the cmpfn.</div><d=
iv><br></div><div>so I have serveral questions:</div><div>1) why in plannin=
g the function volatility is considered but not in execInitAppend;</div><di=
v>2) why timestamptz_xxx_date is STABLE not IMMUTABLE;<br></div><div><br></=
div><div>thanks.<br></div><div><br></div></div></div></div>

--0000000000003423f305d6863d44--