Indeed, this may be useful for partition pruning. I am also curious about why this has not been achieved.
Wenjing <wenjing....@alibaba-inc.com> 于2021年8月23日周一 上午10:46写道： > 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); > > > 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. > > > >
