| Bug #116355 | optimizer trace missing in test_if_cheaper_ordering() | ||
|---|---|---|---|
| Submitted: | 15 Oct 2024 3:23 | Modified: | 17 Sep 16:56 |
| Reporter: | tianfeng li (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Tracing | Severity: | S3 (Non-critical) |
| Version: | 8.0.30,8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, optimizer_trace | ||
[15 Oct 2024 3:24]
tianfeng li
show more detailed optimizer trace information in test_if_cheaper_ordering() (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-show-more-detailed-optimizer-trace-information-in-te.patch (application/octet-stream, text), 2.33 KiB.
[15 Oct 2024 5:24]
MySQL Verification Team
Hello tianfeng li, Thank you for the report and contribution. regards, Umesh
[3 Jul 8:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch pending'
[14 Aug 14:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch approved'
[19 Aug 8:48]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[26 Aug 8:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch pending'
[31 Aug 5:20]
MySQL Admin
Posted by developer: Bug status updated to 'Patch approved'
[31 Aug 14:48]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[17 Sep 16:56]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 26.10.0 release notes: Optimizer trace output now includes a test_if_cheaper_ordering object describing how candidate indexes for ORDER BY or GROUP BY were evaluated. Per-index values include is_covering, select_limit, index_scan_cost, rows, chosen, and the rejection cause. Our thanks to Tianfeng Li and the team at Tencent for the contribution.
Description: Hi, When I attempt to decipher a problem, I wonder why the optimizer selects an unreasonable index in the clause "reconsidering_access_paths_for_index_ordering." The only index I can observe within its nested "rows_estimation" is the idx_bad. However, where are the other indexes that might be more suitable? It appears that they are all concealed within a cost-based index selection function called test_if_cheaper_ordering(). ``` "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ { "rows_estimation": { "table": "`t_func_config`", "index": "idx_bad", "range_analysis": { ... } /* range_analysis */ } /* rows_estimation */ } ] /* steps */, "index_order_summary": {... } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ ``` How to repeat: as description above. Suggested fix: please consider showing more detailed cost based decision in test_if_cheaper_ordering().