GBase 8a 对外部查询条件如涉及到子查询，可以将条件内推到子查询，来减少子查询删选结果的数量，提高性能。
目录导航
参数
_gcluster_optimizer_push_condition说明
说明：查询条件内推优化功能
取值：[0|1|2]
默认值：1
- 0: 不内推
- 1: gcluster层内推
- 2: gnode层内推
使用样例
单表子查询
gbase> show variables like '%_gcluster_optimizer_push_condition%';
+---------------------------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------------------------+-------+
| _gcluster_optimizer_push_condition | 1 |
+---------------------------------------------------------+-------+
1 rows in set (Elapsed: 00:00:00.00)
gbase> explain select * from (select * from t1)t where id<3;
+----+----------+-----------+------------------+-------------+-----------------+
| ID | MOTION | OPERATION | TABLE | CONDITION | NO STAT Tab/Col |
+----+----------+-----------+------------------+-------------+-----------------+
| 00 | [RESULT] | SCAN | testdb.t1_t[DIS] | (id{S} < 3) | t1 |
| | | | | (id{S} < 3) | |
+----+----------+-----------+------------------+-------------+-----------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> set _gcluster_optimizer_push_condition=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> explain select * from (select * from t1)t where id<3;
+----+----------+-----------+------------------+-------------+-----------------+
| ID | MOTION | OPERATION | TABLE | CONDITION | NO STAT Tab/Col |
+----+----------+-----------+------------------+-------------+-----------------+
| 00 | [RESULT] | SCAN | testdb.t1_t[DIS] | (id{S} < 3) | t1 |
+----+----------+-----------+------------------+-------------+-----------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
多表union all子查询
打开参数(默认), id<10的过滤条件，会分别在union all的每个表里做，同时union all后的结果再做一次。
关闭参数后，过滤条件id<10只在union all后做。
gbase> explain select * from (select * from t1 union all select * from t2)tt where tt.id<10;
+----+----------+-------------+---------+--------------+
| ID | MOTION | OPERATION | TABLE | CONDITION |
+----+----------+-------------+---------+--------------+
| 00 | [RESULT] | SubQuery1 | tt | |
| | | SCAN | t1[DIS] | (id{S} < 10) |
| | | UNION ALL | | |
| | | SCAN | t2[DIS] | (id{S} < 10) |
| | | WHERE | | (id < 10) |
+----+----------+-------------+---------+--------------+
5 rows in set (Elapsed: 00:00:00.00)
gbase> explain select * from (select * from t1 union all select * from t2)tt where tt.id<10;
+----+----------+-------------+---------+-----------+
| ID | MOTION | OPERATION | TABLE | CONDITION |
+----+----------+-------------+---------+-----------+
| 00 | [RESULT] | SubQuery1 | tt | |
| | | Table | t1[DIS] | |
| | | UNION ALL | | |
| | | Table | t2[DIS] | |
| | | WHERE | | (id < 10) |
+----+----------+-------------+---------+-----------+
5 rows in set (Elapsed: 00:00:00.01)