Skip to content

Latest commit

 

History

History
187 lines (135 loc) · 11.5 KB

expressions-pushed-down.md

File metadata and controls

187 lines (135 loc) · 11.5 KB
title summary aliases
List of Expressions for Pushdown
Learn a list of expressions that can be pushed down to TiKV and the related operations.
/docs/dev/functions-and-operators/expressions-pushed-down/
/docs/dev/reference/sql/functions-and-operators/expressions-pushed-down/

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 blocklist.

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_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()

Blocklist specific expressions

If unexpected behavior occurs during the calculation of a function caused by its pushdown, you can quickly restore the application by blocklisting that function. Specifically, you can prohibit an expression from being pushed down by adding the corresponding functions or operator to the blocklist mysql.expr_pushdown_blacklist.

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 that is prohibited from being pushed down.
  • store_type: specifies to which storage engine the function is prohibited from being pushed down. Currently, TiDB supports the three storage engines: tikv, tidb, and tiflash. store_type is 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.

Add to the blocklist

To add one or more functions or operators to the blocklist, perform the following steps:

  1. Insert the function or operator name and the collection of storage types to be prohibited from the function pushdown to mysql.expr_pushdown_blacklist.

  2. Execute the admin reload expr_pushdown_blacklist; command.

Remove from the blocklist

To remove one or more functions or operators from the blocklist, perform the following steps:

  1. Delete the function or operator name in mysql.expr_pushdown_blacklist.

  2. Execute the admin reload expr_pushdown_blacklist; command.

Blocklist usage examples

The following example demonstrates how to add the < and > operators 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('<', 'tikv',''), ('>','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_blacklist only 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