List of Expressions for Pushdown
When TiDB reads data from TiKV, TiDB tries to push down some expressions (including calculations of functions or operators) to be processed to TiKV. This reduces the amount of transferred data and offloads processing from a single TiDB node. This document introduces the expressions that TiDB already supports pushing down and how to prohibit specific expressions from being pushed down using blacklist.
Supported expressions for pushdown
| Expression Type | Operations |
|---|---|
| Logical operators | AND (&&), OR (||), NOT (!) |
| Comparison functions and operators | <, <=, =, != (<>), >, >=, <=>, IN(), IS NULL, LIKE, IS TRUE, IS FALSE, COALESCE() |
| Numeric functions and operators | +, -, *, /, ABS(), CEIL(), CEILING(), FLOOR() |
| Control flow functions | CASE, IF(), IFNULL() |
| JSON functions | JSON_TYPE(json_val), JSON_EXTRACT(json_doc, path[, path] …), JSON_UNQUOTE(json_val), JSON_OBJECT(key, val[, key, val] …), JSON_ARRAY([val[, val] …]), JSON_MERGE(json_doc, json_doc[, json_doc] …), JSON_SET(json_doc, path, val[, path, val] …), JSON_INSERT(json_doc, path, val[, path, val] …), JSON_REPLACE(json_doc, path, val[, path, val] …), JSON_REMOVE(json_doc, path[, path] …) |
| Date and time functions | DATE_FORMAT() |
Blacklist specific expressions
If unexpected behavior occurs during the calculation of a function caused by its pushdown, you can quickly restore the application by blacklisting that function. Specifically, you can prohibit an expression from being pushed down by adding the corresponding functions or operator to the blacklist mysql.expr_pushdown_blacklist.
Add to the blacklist
To add one or more functions or operators to the blacklist, perform the following steps:
Insert the function or operator name to
mysql.expr_pushdown_blacklist.Execute the
admin reload expr_pushdown_blacklist;command.
Remove from the blacklist
To remove one or more functions or operators from the blacklist, perform the following steps:
Delete the function or operator name in
mysql.expr_pushdown_blacklist.Execute the
admin reload expr_pushdown_blacklist;command.
Blacklist usage examples
The following example demonstrates how to add the < and > operators to the blacklist, then remove > from the blacklist.
You can see whether the blacklist takes effect by checking the results returned by EXPLAIN statement (See Understanding EXPLAIN results).
tidb> create table t(a int);
Query OK, 0 rows affected (0.01 sec)
tidb> explain select * from t where a < 2 and a > 2;
+---------------------+----------+------+------------------------------------------------------------+
| id | count | task | operator info |
+---------------------+----------+------+------------------------------------------------------------+
| TableReader_7 | 0.00 | root | data:Selection_6 |
| └─Selection_6 | 0.00 | cop | gt(test.t.a, 2), lt(test.t.a, 2) |
| └─TableScan_5 | 10000.00 | cop | table:t, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+----------+------+------------------------------------------------------------+
3 rows in set (0.00 sec)
tidb> insert into mysql.expr_pushdown_blacklist values('<'), ('>');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
tidb> admin reload expr_pushdown_blacklist;
Query OK, 0 rows affected (0.00 sec)
tidb> explain select * from t where a < 2 and a > 2;
+---------------------+----------+------+------------------------------------------------------------+
| id | count | task | operator info |
+---------------------+----------+------+------------------------------------------------------------+
| Selection_5 | 8000.00 | root | gt(test.t.a, 2), lt(test.t.a, 2) |
| └─TableReader_7 | 10000.00 | root | data:TableScan_6 |
| └─TableScan_6 | 10000.00 | cop | table:t, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+----------+------+------------------------------------------------------------+
3 rows in set (0.00 sec)
tidb> delete from mysql.expr_pushdown_blacklist where name = '>';
Query OK, 1 row affected (0.00 sec)
tidb> admin reload expr_pushdown_blacklist;
Query OK, 0 rows affected (0.00 sec)
tidb> explain select * from t where a < 2 and a > 2;
+-----------------------+----------+------+------------------------------------------------------------+
| id | count | task | operator info |
+-----------------------+----------+------+------------------------------------------------------------+
| Selection_5 | 2666.67 | root | lt(test.t.a, 2) |
| └─TableReader_8 | 3333.33 | root | data:Selection_7 |
| └─Selection_7 | 3333.33 | cop | gt(test.t.a, 2) |
| └─TableScan_6 | 10000.00 | cop | table:t, range:[-inf,+inf], keep order:false, stats:pseudo |
+-----------------------+----------+------+------------------------------------------------------------+
4 rows in set (0.00 sec)Note:
admin reload expr_pushdown_blacklistonly takes effect on the TiDB server that executes this SQL statement. To make it apply to all TiDB servers, execute the SQL statement on each TiDB server.- The feature of blacklisting specific expressions is supported in TiDB 3.0.0 or later versions.
- TiDB 3.0.3 or earlier versions does not support adding some of the operators (such as “>”, “+”, “is null”) to the blacklist by using their original names. You need to use their aliases (case-insensitive) instead, as shown in the following table:
| Operator Name | Aliases |
|---|---|
| < | LT |
| > | GT |
| <= | LE |
| >= | GT |
| = | EQ |
| != | NE |
| <> | NE |
| <=> | NullEQ |
| | | bitor |
| && | bitand |
| || | or |
| ! | not |
| in | IN |
| + | PLUS |
| - | MINUS |
| * | MUL |
| / | DIV |
| DIV | INTDIV |
| IS NULL | ISNULL |
| IS TRUE | ISTRUE |
| IS FALSE | ISFALSE |