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

2021年8月16日 17:15，Wenjing <wenjing.zwj@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.