Bug #103551 make query planer aware of the read pattern (random/sequential)
Submitted: 2 May 2021 13:10 Modified: 3 May 2021 6:53
Reporter: haochen he Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance

[2 May 2021 13:10] haochen he
Description:
The query planner will calculate the cost of a query plan.
One important part of the cost model is the estimated rows read from the disk.
For example, this part of the cost of the following plan is :
  
    io_block_read_cost * Σ(rows * filtered)

+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |     1.33 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |     0.67 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

However, no matter these reads are sequential or random, the cost will be same.
Given that the speed of sequential read and random read can be very different in different storage media.
If the query planer treats them in the same way, there are more likely that a sub-optimal query plan can be chosen, for example, I encounter this case:

    bug #103272

I think this is not a corner case.
I noticed that the `engine_cost` table contains a `device_type` column,
could I expect MySQL will take these different read characteristics among different devices into account?

Thanks!

How to repeat:
None.

Suggested fix:
Make query planer aware of the read pattern (random/sequential)
[3 May 2021 6:53] MySQL Verification Team
Hi haochen he,

Thank you for your feature request.

regards,
Umesh