| title | summary | aliases | ||
|---|---|---|---|---|
List of Expressions for Pushdown |
Learn a list of expressions that can be pushed down to TiKV and the related operations. |
|
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 blocklist.
Tiflash also supports pushdown for the functions and operators listed on this page.
| 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(), MOD() |
| Control flow functions | CASE, IF(), IFNULL() |
| JSON functions | JSON_TYPE(json_val), JSON_EXTRACT(json_doc, path[, path] ...), 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(), SYSDATE() |
| String functions | RIGHT() |
If unexpected behavior occurs in the calculation process when pushing down the supported expressions or specific data types (only the ENUM type and the BIT type), you can restore the application quickly by prohibiting the pushdown of the corresponding functions, operators, or data types. Specifically, you can prohibit the functions, operators, or data types from being pushed down by adding them to the blocklist mysql.expr_pushdown_blacklist. For details, refer to Add to the blocklist.
The schema of mysql.expr_pushdown_blacklist is as follows:
tidb> desc mysql.expr_pushdown_blacklist;
+------------+--------------+------+------+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+------+-------------------+-------+
| name | char(100) | NO | | NULL | |
| store_type | char(100) | NO | | tikv,tiflash,tidb | |
| reason | varchar(200) | YES | | NULL | |
+------------+--------------+------+------+-------------------+-------+
3 rows in set (0.00 sec)Field description:
name: the name of the function, operator, or data type that is prohibited from being pushed down.store_type: specifies to which storage engine the function, operator, or data type is prohibited from being pushed down. Currently, TiDB supports the three storage engines:tikv,tidb, andtiflash.store_typeis case-insensitive. If a function is prohibited from being pushed down to multiple storage engines, use a comma to separate each engine.reason: The reason why the function is blocklisted.
To add one or more functions, operators, or data types (only the ENUM type and the BIT type) to the blocklist, perform the following steps:
-
Insert the followings to
mysql.expr_pushdown_blacklist:- the name of the function, operator, or data type to be prohibited from being pushed down
- the storage engine to be prohibited from being pushed down
-
Execute the
admin reload expr_pushdown_blacklist;command.
To remove one or more functions, operators, or data types from the blocklist, perform the following steps:
-
Delete the name of the function, operator, or data type in
mysql.expr_pushdown_blacklist. -
Execute the
admin reload expr_pushdown_blacklist;command.
The following example demonstrates how to add the DATE_FORMAT() function, > operator, and BIT data type to the blocklist, then remove > from the blocklist.
You can see whether the blocklist 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.06 sec)
tidb> explain select * from t where a < 2 and a > 2;
+-------------------------+----------+-----------+---------------+------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------+
| TableReader_7 | 0.00 | root | | data:Selection_6 |
| └─Selection_6 | 0.00 | cop[tikv] | | gt(ssb_1.t.a, 2), lt(ssb_1.t.a, 2) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+------------------------------------+
3 rows in set (0.00 sec)
tidb> insert into mysql.expr_pushdown_blacklist values('date_format()', 'tikv',''), ('>','tikv',''), ('bit','tikv','');
Query OK, 2 rows affected (0.01 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 | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------+
| Selection_7 | 10000.00 | root | | gt(ssb_1.t.a, 2), lt(ssb_1.t.a, 2) |
| └─TableReader_6 | 10000.00 | root | | data:TableFullScan_5 |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | 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.01 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 | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+--------------------------------+
| Selection_8 | 0.00 | root | | lt(ssb_1.t.a, 2) |
| └─TableReader_7 | 0.00 | root | | data:Selection_6 |
| └─Selection_6 | 0.00 | cop[tikv] | | gt(ssb_1.t.a, 2) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | 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 blocklisting 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 blocklist by using their original names. You need to use their aliases (case-sensitive) instead, as shown in the following table:
| Operator Name | Aliases |
|---|---|
| < | lt |
| > | gt |
| <= | le |
| >= | ge |
| = | 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 |