test plan effectiveness: output all the considered query plan and execute a specific one

[Hexilee]

Feature Request

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

For measuring the effectiveness of optimizer.

Describe the feature you'd like:

proposal : doc

nth_plan(x)

select /*+ nth_plan(x) */ * from t;

• Indicate the optimizer to use the x_th physical plan generated. (referred as plan's rank in the following paragraphs)
• Users can take a look at the details by executing explain with nth_plan(x).
• An error will be thrown if x is too big.

explain format = verbose

explain format = verbose select * from t;

Add two parameters to the result of explain:

• PlanCount : the number of plans found during optimizing.
• BestPlanId : the rank of the best plan. (based on estimated cost)

Teachability, Documentation, Adoption, Migration Strategy:

• test the estimate of the plan cost.
• help choose the best plan manually.

steps

• statistic the number of plans found in findBestTask.
• reallize countdown in findBestTask.
• add a SQL hint to the parser.
• add test cases for the hint and plan number statistic.
• add explain format = verbose.

[zz-jason]

I think we can close this issue since nth_plan has been supported.

[SunRunAway]

@time-and-fate Would you like to add the corresponding documentation for this PR in pingcap/docs-cn sometime?

[time-and-fate]

@time-and-fate Would you like to add the corresponding documentation for this PR in pingcap/docs-cn sometime?

@SunRunAway Sure. I'll try to do it after I complete my current assignment.