Cascades planner cost estimates off on simple range conditions

[wwar]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

I had a problem with a more complex query not picking the right index (for another report..). I thought I would try the cascades planner for debugging purposes, but noticed it was actually much worse. I've isolated it to a poor cost decision.

Consider the following test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id BIGINT NOT NULL PRIMARY KEY auto_increment,
 b BIGINT NOT NULL,
 pad VARBINARY(255),
 INDEX (b)
);

INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM dual;
INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM t1 a INNER JOIN t1 b INNER JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM t1 a INNER JOIN t1 b INNER JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM t1 a INNER JOIN t1 b INNER JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM t1 a INNER JOIN t1 b INNER JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, 1, RANDOM_BYTES(255) FROM t1 a INNER JOIN t1 b INNER JOIN t1 c LIMIT 10000;
UPDATE t1 SET b=id;
SELECT SLEEP(5);
ANALYZE TABLE t1;
SET tidb_enable_cascades_planner=0;
EXPLAIN ANALYZE SELECT * FROM t1 WHERE b < 5;
SET tidb_enable_cascades_planner=1;
EXPLAIN ANALYZE SELECT * FROM t1 WHERE b < 5;

(The sleep call is due to a different bug: #16570 )

2. What did you expect to see?

This is the non-cascades planner:

mysql> SET tidb_enable_cascades_planner=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE b < 5;
+-------------------------------+---------+---------+-----------+----------------------+------------------------------------------------------------------------------+----------------------------------+---------------+------+
| id                            | estRows | actRows | task      | access object        | execution info                                                               | operator info                    | memory        | disk |
+-------------------------------+---------+---------+-----------+----------------------+------------------------------------------------------------------------------+----------------------------------+---------------+------+
| IndexLookUp_10                | 4.00    | 4       | root      |                      | time:13.980443ms, loops:2, rpc num: 1, rpc time:13.446407ms, proc keys:21013 |                                  | 10.0234375 KB | N/A  |
| ├─IndexRangeScan_8(Build)     | 4.00    | 4       | cop[tikv] | table:t1, index:b(b) | time:12ms, loops:1                                                           | range:[-inf,5), keep order:false | N/A           | N/A  |
| └─TableRowIDScan_9(Probe)     | 4.00    | 4       | cop[tikv] | table:t1             | time:0s, loops:1                                                             | keep order:false                 | N/A           | N/A  |
+-------------------------------+---------+---------+-----------+----------------------+------------------------------------------------------------------------------+----------------------------------+---------------+------+
3 rows in set (0.01 sec)

3. What did you see instead?

With the cascades planner:

mysql> SET tidb_enable_cascades_planner=1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE b < 5;
+-------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------+------------------+-----------------+------+
| id                      | estRows  | actRows | task      | access object | execution info                                                               | operator info    | memory          | disk |
+-------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------+------------------+-----------------+------+
| TableReader_7           | 16808.00 | 4       | root      |               | time:25.429191ms, loops:2, rpc num: 1, rpc time:25.357631ms, proc keys:21010 | data:Selection_8 | 1.7861328125 KB | N/A  |
| └─Selection_8           | 16808.00 | 4       | cop[tikv] |               | time:24ms, loops:25                                                          | lt(test.t1.b, 5) | N/A             | N/A  |
|   └─TableFullScan_9     | 21010.00 | 21010   | cop[tikv] | table:t1      | time:24ms, loops:25                                                          | keep order:false | N/A             | N/A  |
+-------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------+------------------+-----------------+------+
3 rows in set (0.03 sec)

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

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-259-g2c8afe6e1
Git Commit Hash: 2c8afe6e1621d4bc2074d17f6c09c17f99fd4d89
Git Branch: master
UTC Build Time: 2020-04-15 03:14:55
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[Reminiscent]

/assign