GBase 8a参数_gcluster_optimizer_push_condition介绍

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)