Re: Is it worth pushing conditions to sublink/subplan?

> 2021年8月16日 17:15，Wenjing <wenjing(dot)zwj(at)alibaba-inc(dot)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.
>
>
>