Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
In MySQL, Optimizer trace is a JSON object recording the execution path through the optimizer, decisions that were made and the reasons for them. See https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
Users were asking for MariaDB to have a similar feature.
Attachments
Issue Links
- blocks
-
-
- Closed
-
- includes
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-18117 Crash with Explain extended when using limit rows examined
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
Activity
Pushed this:
https://github.com/MariaDB/server/commit/2dbe472ed011a951b28434ae8e67945e964d2030
Optimizer trace: print cost and #rows of the join prefix
|
|
|
The names rows_for_plan and cost_for_plan follow MySQL
|
Also added post-join-operation selectivity cost
|
WIth holyfoot's and Serg's input:
mysql> select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from t20\G
|
*************************** 1. row ***************************
|
JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')): [
|
|
{
|
"table_scan":
|
{
|
"rows": 1000,
|
"cost": 103.1
|
},
|
"potential_range_indexes":
|
[
|
|
{
|
"index": "a",
|
"usable": true,
|
"key_parts":
|
[
|
"a"
|
]
|
}
|
],
|
"setup_range_conditions":
|
[
|
],
|
so, it is possible to extract parts of trace using JSON function. You'll need to re-format them, and formatter is not perfect, but still.
As for pretty-printing, there is JSON_DETAILED function but its output is not as beautiful as the one that EXPLAIN FORMAT=JSON pretty-printer produces.
Example:
|
|
|
"key_parts":
|
[
|
"a"
|
]
|
}
|
],
|
"setup_range_conditions":
|
[
|
],
|
... However, MariaDB's JSON function do not allow to compute JSON paths? I tried JSON_EXTRACT and others, none could achieve what I wanted - extract all nodes in the document with the name range_analysis.
Pushed this:
http://lists.askmonty.org/pipermail/commits/2019-August/013945.html
http://lists.askmonty.org/pipermail/commits/2019-August/013945.htmlcommit 4a490d1a993959222deb8bae822f40575586a148 (HEAD -> 10.4, origin/HEAD, origin/10.4, tmp10)Author: Sergei Petrunia <psergey@askmonty.org>Date: Sun Aug 25 11:03:19 2019 +0300MDEV-6111: Optimizer Trace: add tracing for semi-join optimizationsAdded:- "semijoin_strategy_choice" element (actions in advance_sj_state(), namematches the name in MySQL)- semijoin_table_pullout element.UPDATE: filed a MDEV to track the above patch:
MDEV-20440.