> 2021年8月16日 17:15，Wenjing <wenjing....@alibaba-inc.com> 写道： > > Hi Hackers, > > Recently, a issue has been bothering me, This is about conditional push-down > in SQL. > I use cases from regression testing as an example. > I found that the conditions (B =1) can be pushed down into the subquery, > However, it cannot be pushed down to sublink/subplan. > If a sublink/subplan clause contains a partition table, it can be useful to > get the conditions for pruning. > So, is it worth pushing conditions to sublink/subplan? > Anybody have any ideas? > > > regards, > Wenjing > > > example: > create table p (a int, b int, c int) partition by list (a); > create table p1 partition of p for values in (1); > create table p2 partition of p for values in (2); > create table q (a int, b int, c int) partition by list (a); > create table q1 partition of q for values in (1) partition by list (b); > create table q11 partition of q1 for values in (1) partition by list (c); > create table q111 partition of q11 for values in (1); > create table q2 partition of q for values in (2) partition by list (b); > create table q21 partition of q2 for values in (1); > create table q22 partition of q2 for values in (2); > insert into q22 values (2, 2, 3); Sorry, I messed up the structure of the table. It is should be: create table ab (a int not null, b int not null) partition by list (a); create table ab_a2 partition of ab for values in(2) partition by list (b); create table ab_a2_b1 partition of ab_a2 for values in (1); create table ab_a2_b2 partition of ab_a2 for values in (2); create table ab_a2_b3 partition of ab_a2 for values in (3); create table ab_a1 partition of ab for values in(1) partition by list (b); create table ab_a1_b1 partition of ab_a1 for values in (1); create table ab_a1_b2 partition of ab_a1 for values in (2); create table ab_a1_b3 partition of ab_a1 for values in (3); create table ab_a3 partition of ab for values in(3) partition by list (b); create table ab_a3_b1 partition of ab_a3 for values in (1); create table ab_a3_b2 partition of ab_a3 for values in (2); create table ab_a3_b3 partition of ab_a3 for values in (3);
> > > postgres-# explain (costs off) > postgres-# select temp.b from > postgres-# ( > postgres(# select a,b from ab x where x.a = 1 > postgres(# union all > postgres(# (values(1,1)) > postgres(# ) temp, > postgres-# ab y > postgres-# where y.b = temp.b and y.a = 1 and y.b=1; > QUERY PLAN > --------------------------------------------------- > Nested Loop > -> Seq Scan on ab_a1_b1 y > Filter: ((b = 1) AND (a = 1)) > -> Append > -> Subquery Scan on "*SELECT* 1" > -> Seq Scan on ab_a1_b1 x > Filter: ((a = 1) AND (b = 1)) > -> Result > (8 rows) > > The conditions (B =1) can be pushed down into the subquery. > > postgres=# explain (costs off) > postgres-# select > postgres-# y.a, > postgres-# (Select x.b from ab x where y.a =x.a and y.b=x.b) as b > postgres-# from ab y where a = 1 and b = 1; > QUERY PLAN > --------------------------------------------------- > Seq Scan on ab_a1_b1 y > Filter: ((a = 1) AND (b = 1)) > SubPlan 1 > -> Append > -> Seq Scan on ab_a1_b1 x_1 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a1_b2 x_2 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a1_b3 x_3 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b1 x_4 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b2 x_5 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b3 x_6 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b1 x_7 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b2 x_8 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b3 x_9 > Filter: ((y.a = a) AND (y.b = b)) > (22 rows) > > The conditions (B = 1 and A = 1) cannot be pushed down to sublink/subplan in > targetlist. > > postgres=# explain (costs off) > postgres-# select y.a > postgres-# from ab y > postgres-# where > postgres-# (select x.a > x.b from ab x where y.a =x.a and y.b=x.b) and > postgres-# y.a = 1 and y.b = 1; > QUERY PLAN > --------------------------------------------------- > Seq Scan on ab_a1_b1 y > Filter: ((a = 1) AND (b = 1) AND (SubPlan 1)) > SubPlan 1 > -> Append > -> Seq Scan on ab_a1_b1 x_1 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a1_b2 x_2 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a1_b3 x_3 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b1 x_4 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b2 x_5 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a2_b3 x_6 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b1 x_7 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b2 x_8 > Filter: ((y.a = a) AND (y.b = b)) > -> Seq Scan on ab_a3_b3 x_9 > Filter: ((y.a = a) AND (y.b = b)) > (22 rows) > > The conditions (B=1 and A=1) cannot be pushed down to sublink/subplan in > where clause. > > >
smime.p7s
Description: S/MIME cryptographic signature
