GBase 8a Parameter: _gcluster_optimizer_push_condition

In GBase 8a, the _gcluster_optimizer_push_condition parameter controls whether outer query conditions are pushed down into subqueries. This optimization reduces the number of rows processed inside subqueries, significantly improving query performance. The parameter accepts three values, with the default being 1.

Parameter Details

• Name: _gcluster_optimizer_push_condition
• Values: 0 (disable pushdown), 1 (pushdown at the gcluster layer, default), 2 (pushdown at the gnode layer)
• Default: 1

When enabled, the optimizer pushes outer WHERE filters into subqueries, allowing data to be filtered at the earliest possible stage instead of scanning the entire subquery result and then applying the condition.

Usage Examples

Single-Table Subquery

Default (parameter=1) execution plan: The outer condition id<3 is pushed into the subquery and applied directly during the table scan.

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

After disabling (parameter=0): The filter is no longer pushed down; it is only applied to the final result set.

gbase> set _gcluster_optimizer_push_condition=0;
Query OK, 0 rows affected

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

Subquery with UNION ALL

When the parameter is enabled, the filter id<10 is pushed into each branch of the UNION ALL, filtering rows during the individual table scans. A final filter is also applied after the union.

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

With the parameter off, the UNION ALL first combines the full contents of both tables, and only then applies the id<10 filter.

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

Leveraging condition pushdown is a crucial tuning technique in a gbase database, especially when dealing with complex subqueries and large data volumes. By pushing filters early, you minimise the amount of data that flows through the execution pipeline, keeping your GBASE cluster efficient and responsive.