lightdb-no_push_subq

LightDB 从 23.4 版本开始支持no_push_subq hint，在 LightDB 中下推过滤条件是RBO，会尽量下推过滤条件以过滤尽可能多的数据。通过使用这个 hint 可以强制不下推带子链接的过滤条件（带子链接的过滤条件可能效率较差）。

需要注意的是，对于被pull up 后的子链接不起效（因为已经不是过滤条件了）。

下面是使用示例：

示例

create table test_no_push_subq1 as select * from pg_class order by oid limit 100;
create table test_no_push_subq2 as select * from pg_class order by oid limit 100;
create table test_no_push_subq3 as select * from pg_class order by oid limit 100;
create table test_no_push_subq4 as select * from pg_class order by oid limit 100;

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2);
                  QUERY PLAN                  
----------------------------------------------
 Nested Loop
   Join Filter: (a.reltype = b.reltype)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_no_push_subq2
   ->  Seq Scan on test_no_push_subq1 a
         Filter: (oid = $0)
   ->  Materialize
         ->  Seq Scan on test_no_push_subq3 b
               Filter: (oid = $0)
(10 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/ max(oid) from test_no_push_subq2);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (a.oid = $0)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_no_push_subq2 @"lt#0"
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
(9 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2 c where a.oid = c.oid);
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Nested Loop
   Join Filter: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq3 b
   ->  Materialize
         ->  Seq Scan on test_no_push_subq1 a
               Filter: (oid = (SubPlan 2))
               SubPlan 2
                 ->  Result
                       InitPlan 1 (returns $1)
                         ->  Limit
                               ->  Seq Scan on test_no_push_subq2 c
                                     Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/  max(oid) from test_no_push_subq2 c where a.oid = c.oid);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (a.oid = (SubPlan 2))
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 2
     ->  Result
           InitPlan 1 (returns $1)
             ->  Limit
                   ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                         Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
                      QUERY PLAN                      
------------------------------------------------------
 Nested Loop
   Join Filter: (a.reltype = b.reltype)
   ->  Seq Scan on test_no_push_subq3 b
   ->  Materialize
         ->  Seq Scan on test_no_push_subq1 a
               Filter: ((SubPlan 1) = (SubPlan 2))
               SubPlan 1
                 ->  Seq Scan on test_no_push_subq2 c
                       Filter: (oid = a.oid)
               SubPlan 2
                 ->  Seq Scan on test_no_push_subq3 d
                       Filter: (oid = a.oid)
(12 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select/*+no_push_subq*/ oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
                      QUERY PLAN                      
------------------------------------------------------
 Hash Join
   Hash Cond: (a.reltype = b.reltype)
   Join Filter: ((SubPlan 1) = (SubPlan 2))
   ->  Seq Scan on test_no_push_subq1 a @"lt#2"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#2"
   SubPlan 1
     ->  Seq Scan on test_no_push_subq2 c @"lt#0"
           Filter: (oid = a.oid)
   SubPlan 2
     ->  Seq Scan on test_no_push_subq3 d @"lt#1"
           Filter: (oid = a.oid)
(12 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select oid from test_no_push_subq2 c where c.oid =a.oid);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq1 a
         Filter: (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on test_no_push_subq2 c
                 Filter: (oid = a.oid)
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b
(9 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select/*+ no_push_subq*/ oid from test_no_push_subq2 c where c.oid =a.oid);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (SubPlan 1)
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 1
     ->  Seq Scan on test_no_push_subq2 c @"lt#0"
           Filter: (oid = a.oid)
(9 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
                          QUERY PLAN                           
---------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
         Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
         SubPlan 1
           ->  Result
                 One-Time Filter: (a.oid = '2691'::oid)
                 ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                       Filter: (oid = '2691'::oid)
         SubPlan 2
           ->  Seq Scan on test_no_push_subq2 c_1 @"lt#0"
                 Filter: (oid = '2691'::oid)
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
(14 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest no_push_subq*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
                          QUERY PLAN                          
--------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 1
     ->  Result
           One-Time Filter: (a.oid = '2691'::oid)
           ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                 Filter: (oid = '2691'::oid)
   SubPlan 2
     ->  Seq Scan on test_no_push_subq2 c_1 @"lt#0"
           Filter: (oid = '2691'::oid)
(14 rows)

lightdb@postgres=# 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194