[plan cache] cannot use point get plan when plan cache enabled

[zz-jason]

Performance Improvement

1. Reproduce Steps

• step 1: start a tidb-server with mocktikv and plan cache enabled

• step 2: run the following statements in a connection:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    a     BIGINT,
    b     VARCHAR(40),
    col3  VARCHAR(40) DEFAULT NULL,
    col4  VARCHAR(40) DEFAULT NULL,
    col5  VARCHAR(40) DEFAULT NULL,
    col6  VARCHAR(40) DEFAULT NULL,
    col7  VARCHAR(40) DEFAULT NULL,
    col8  VARCHAR(40) DEFAULT NULL,
    col9  VARCHAR(40) DEFAULT NULL,
    col10 VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY (a, b)
);
INSERT INTO t1(a, b) VALUES (1, "1");
INSERT INTO t1(a, b) VALUES (1, "2");
INSERT INTO t1(a, b) VALUES (2, "1");
INSERT INTO t1(a, b) VALUES (2, "2");
INSERT INTO t1(a, b) VALUES (3, "1");
INSERT INTO t1(a, b) VALUES (3, "2");

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
    a     BIGINT,
    b     VARCHAR(40),
    c     VARCHAR(40),
    col4  VARCHAR(40) DEFAULT NULL,
    col5  VARCHAR(40) DEFAULT NULL,
    col6  VARCHAR(40) DEFAULT NULL,
    col7  VARCHAR(40) DEFAULT NULL,
    col8  VARCHAR(40) DEFAULT NULL,
    col9  VARCHAR(40) DEFAULT NULL,
    col10 VARCHAR(40) DEFAULT NULL,
    UNIQUE KEY idx_a (a)
);
INSERT INTO t2(a, b, c) VALUES(1, "b", "c");
INSERT INTO t2(a, b, c) VALUES(2, "b", "c");
INSERT INTO t2(a, b, c) VALUES(3, "b", "c");

prepare stmt from "
    select * from t1, t2
    where t1.a = t2.a
    and t2.a = ?
    and t2.b = 'b'
    and t2.c = 'c'
";

set @a = 1;
execute stmt using @a;
execute stmt using @a;

The last statements is using plan cache, you can confirm that by executing select @@last_plan_from_cache.

2. What did you see instead?

The plan of the statements is:

TiDB(root@127.0.0.1:test) > explain for connection 8;
+--------------------------------------+----------+-----------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object                 | operator info                                                                                                                                                                                                                                                             |
+--------------------------------------+----------+-----------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7                         | 0.01     | root      |                               | test.t1.a, test.t1.b, test.t1.col3, test.t1.col4, test.t1.col5, test.t1.col6, test.t1.col7, test.t1.col8, test.t1.col9, test.t1.col10, test.t2.a, test.t2.b, test.t2.c, test.t2.col4, test.t2.col5, test.t2.col6, test.t2.col7, test.t2.col8, test.t2.col9, test.t2.col10 |
| └─IndexMergeJoin_20                  | 0.01     | root      |                               | inner join, inner:Projection_18, outer key:test.t2.a, inner key:test.t1.a                                                                                                                                                                                                 |
|   ├─TableReader_54(Build)            | 0.01     | root      |                               | data:Selection_53                                                                                                                                                                                                                                                         |
|   │ └─Selection_53                   | 0.01     | cop[tikv] |                               | eq(cast(test.t2.a), 1), eq(test.t2.b, "b"), eq(test.t2.c, "c"), not(isnull(test.t2.a))                                                                                                                                                                                    |
|   │   └─TableFullScan_52             | 10000.00 | cop[tikv] | table:t2                      | keep order:false, stats:pseudo                                                                                                                                                                                                                                            |
|   └─Projection_18(Probe)             | 1.25     | root      |                               | test.t1.a, test.t1.b, test.t1.col3, test.t1.col4, test.t1.col5, test.t1.col6, test.t1.col7, test.t1.col8, test.t1.col9, test.t1.col10                                                                                                                                     |
|     └─IndexLookUp_17                 | 1.25     | root      |                               |                                                                                                                                                                                                                                                                           |
|       ├─Selection_16(Build)          | 1.25     | cop[tikv] |                               | eq(cast(test.t1.a), 1)                                                                                                                                                                                                                                                    |
|       │ └─IndexRangeScan_14          | 1.56     | cop[tikv] | table:t1, index:PRIMARY(a, b) | range: decided by [eq(test.t1.a, test.t2.a)], keep order:true, stats:pseudo                                                                                                                                                                                               |
|       └─TableRowIDScan_15(Probe)     | 1.25     | cop[tikv] | table:t1                      | keep order:false, stats:pseudo                                                                                                                                                                                                                                            |
+--------------------------------------+----------+-----------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

The plan is not a Point_Get plan is because of the comparison between bigint and string values results in casting bigint value to string, which can be confirmed by the operator info of Selection_53: eq(cast(test.t2.a), 1).

But if we executing the following SQL, the query can benefit from point get:

create table t(a bigint unique key, b bigint); -- NOTE the bigint type here

TiDB(root@127.0.0.1:test) > explain select * from t where a = "1";
+-------------+---------+------+---------------------+---------------+
| id          | estRows | task | access object       | operator info |
+-------------+---------+------+---------------------+---------------+
| Point_Get_1 | 1.00    | root | table:t, index:a(a) |               |
+-------------+---------+------+---------------------+---------------+
1 row in set (0.00 sec)

From the user's angle, this query and the prepared query should have the same behavior: trying to convert string to int and utilize the Point_Get plan.

This further cause the above prepared query executes faster when the plan cache is disabled, because it can utilize the Point_Get plan.

3. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

➜ ./bin/tidb-server -V
Release Version: v4.0.0-beta.2-352-g5ede18f10
Git Commit Hash: 5ede18f10eedfe2e3690d7728dec3ffa4b0af2d5
Git Branch: master
UTC Build Time: 2020-04-25 08:41:26
GoVersion: go1.13.10
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[eurekaka]

This is same problem as #16676 (comment). Briefly speaking, we cannot apply those optimizations which are too aggressive when plan cache is enabled(refineArgs here), i.e, the parameter may change during next execution, we have to maintain the original expression for re-evaluation.

[eurekaka]

We have 2 approaches to fix this problem:

• differentiate types for prepared parameters, this solves the problem from the beginning;
• extract logics in refineArgs to a builtin scalar function, and save this function as DeferredExpr in the refined constant, so that it can be re-evaluated when the prepared parameter is changed;