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.
smime.p7s
Description: S/MIME cryptographic signature
