consider region distribution in optimization phase

[qw4990]

Feature Request

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

In the IMDB test, we found a case that two full index scans on different indexes with limitation have a huge gap on performance.

In this case, we only need the first cop-task's data since there is a Limit 100. And because these two indexes have different region distributions, the number of rows need to scan to finish the first cop-task are different.

Describe the feature you'd like:

Consider region distribution in optimization phase for this case.

NOTE: introducing this physical information(region distribution) into the optimizer seems tricky, we need more discussion about this feature.

Describe alternatives you've considered:

No

Teachability, Documentation, Adoption, Migration Strategy:

[qw4990]

What are your opinions? @zz-jason @eurekaka @winoros

[eurekaka]

That is what I mean in https://github.com/pingcap/tidb/blob/master/planner/core/find_best_task.go#L1577. Would the result change if we use stream coprocessor request?

[zz-jason]

highly recommended to support this in the query optimizer:

• region distribution stored in PD is nearly the real-time information, we can use it to adjust the selectivity estimation if possible
• we can also take the network rpc times into the cost model
• the regions can be regarded as a huge equal-depth histogram because of the region size limitation, if we can store moge information in a single region info, like NDV, avg mvcc keys, we can make the cost model more accurate as well.

[qw4990]

@tangwz and I will draft a proposal for this feature soon.

[tangwz]

/assign.