[planner] proposal: use tiny benchmark to adjust default CBO factor.

[hanfei1991]

Feature Request

Is your feature request related to a problem? Please describe:

There are some cases that can't choose right storage enigne, like(table name is censored) : explain analyze SELECT SUM(amount) FROM XXXXXX WHERE shop_id=140193611; There is an index with column shop_id. As a result , cbo selects IndexLookUp

+----------------------------------+-------------+----------+-----------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------------------+------+
| id                               | estRows     | actRows  | task      | access object                                                                      | execution info                                                                                                                                                                                                                                             | operator info                                                       | memory               | disk |
+----------------------------------+-------------+----------+-----------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------------------+------+
| HashAgg_21                       | 1.00        | 1        | root      |                                                                                    | time:2m19.959982717s, loops:2, PartialConcurrency:4, FinalConcurrency:4                                                                                                                                                                                    | funcs:sum(Column#27)->Column#19                                     | 11.15625 KB          | N/A  |
| └─IndexLookUp_22             | 1.00        | 103710   | root      |                                                                                    | time:2m19.948722058s, loops:3242, cop_task: {num: 52, max: 1.280856095s, min: 373.437597ms, avg: 799.675993ms, p95: 1.246558498s, max_proc_keys: 1411951, p95_proc_keys: 1262684, tot_proc: 37.906s, tot_wait: 21ms, rpc_num: 52, rpc_time: 41.582830026s} |                                                                     | 240.2413034439087 MB | N/A  |
|   ├─IndexRangeScan_17(Build) | 47659388.91 | 48429215 | cop[tikv] | table:XXXXXX, index:XXXXXX(shop_id, item_id, user_id) | proc max:1.05s, min:342ms, p80:828ms, p95:1.011s, iters:47531, tasks:52                                                                                                                                                                                    | range:[140193611,140193611], keep order:false                       | N/A                  | N/A  |
|   └─HashAgg_7(Probe)         | 1.00        | 0        | cop[tikv] |                                                                                    | time:0ns, loops:0                                                                                                                                                                                                                                          | funcs:sum(XXXXXX.order_item_tab.amount)->Column#27 | N/A                  | N/A  |
|     └─TableRowIDScan_18      | 47659388.91 | 0        | cop[tikv] | table:XXXXXX                                                               | time:0ns, loops:0                                                                                                                                                                                                                                          | keep order:false                                                    | N/A                  | N/A  |
+----------------------------------+-------------+----------+-----------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------------------+------+
5 rows in set (2 min 19.96 sec)

But If we choose to read TiFlash by hint, it only takes 5.77 seconds.

+-------------------------------+---------------+------------+--------------+----------------------+---------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+---------------+------+
| id                            | estRows       | actRows    | task         | access object        | execution info                                                                                    | operator info                                                       | memory        | disk |
+-------------------------------+---------------+------------+--------------+----------------------+---------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+---------------+------+
| StreamAgg_20                  | 1.00          | 1          | root         |                      | time:5.716064079s, loops:2                                                                        | funcs:sum(Column#21)->Column#19                                     | 1.39453125 KB | N/A  |
| └─TableReader_21          | 1.00          | 2          | root         |                      | time:5.716046163s, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s}                | data:StreamAgg_9                                                    | 147 Bytes     | N/A  |
|   └─StreamAgg_9           | 1.00          | 2          | cop[tiflash] |                      | proc max:5.156096965s, min:5.072046905s, p80:5.156096965s, p95:5.156096965s, iters:2, tasks:2     | funcs:sum(XXXXXX.order_item_tab.amount)->Column#21 | N/A           | N/A  |
|     └─Selection_19        | 48670965.88   | 48423954   | cop[tiflash] |                      | proc max:5.153096909s, min:5.05104671s, p80:5.153096909s, p95:5.153096909s, iters:89842, tasks:2  | eq(XXXXXX.order_item_tab.shop_id, 140193611)       | N/A           | N/A  |
|       └─TableRangeScan_18 | 7429274140.00 | 5279783642 | cop[tiflash] | table:XXXXXX | proc max:4.993093897s, min:4.846044813s, p80:4.993093897s, p95:4.993093897s, iters:94577, tasks:2 | range:[0,+inf], keep order:false                                    | N/A           | N/A  |
+-------------------------------+---------------+------------+--------------+----------------------+---------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+---------------+------+
5 rows in set (5.77 sec)

Describe the feature you'd like:

As described, although the scan amounts in TiFlash is 100 times larger than TiDB index, it still takes 100 times less time than TiDB takes. Incorrect default factor has a great nagetive impacts on cbo selections. Surely we can adjust cbo factor case by case, but as the supported operators grow more and more in different engines. Should we continue to set cbo factor according to experieces instead of experiments ?

In order to address this issue, we should provide an new statement as adjust cbo factor automatically; then run a series of benchmark statement to compare the speed among tablescan,index scan,index look up and tiflash scan , including index join, hash join and tiflash broadcast join.

Describe alternatives you've considered:

Indeed , our aim is to prevent a worse plan chosen by cbo automatically. Once it happens, we should provide user some approches to tell optimizer "For a certain statement, please choose this plan". We can do this like explain designate plan [sql statement with hint] Teachability, Documentation, Adoption, Migration Strategy:

[zz-jason]

The basic idea sounds good to me. How other databases perform this operation? Do they have a similar SQL syntax?

[hanfei1991]

@zz-jason I'm not sure. I figure the similar issues only happens on the dbs which supports multiple isolated engines. They are deployed on different machines, with different types of disks or net cards. I think it's rare in open-source databases; anyway we can dive into it further.

[zz-jason]

Got it. I think we need more investigation before accepting this feature request. Suggestions are welcomed.