Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
-
10.1.6-1
Description
EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
An example query:
MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
|
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_time_ms": 2497.7,
|
"table": {
|
"table_name": "customer",
|
"access_type": "index",
|
"key": "i_c_nationkey",
|
"key_length": "5",
|
"used_key_parts": ["c_nationkey"],
|
"r_loops": 1,
|
"rows": 150747,
|
"r_rows": 150000,
|
"r_time_ms": 34.957,
|
"filtered": 100,
|
"r_filtered": 60.691,
|
"attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
|
"using_index": true
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 150000,
|
"r_time_ms": 2395.5,
|
"table": {
|
"table_name": "orders",
|
"access_type": "ref",
|
"possible_keys": ["i_o_custkey"],
|
"key": "i_o_custkey",
|
"key_length": "5",
|
"used_key_parts": ["o_custkey"],
|
"ref": ["dbt3sf1.customer.c_custkey"],
|
"r_loops": 150000,
|
"rows": 7,
|
"r_rows": 10,
|
"r_time_ms": 2120.7,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
]
|
}
|
}
|
EXPLAIN EXTENDED+subquery cache shows the query uses subquery cache.
- EXPLAIN FORMAT=JSON should show it: subquery's query_block should be wrapped inside subquery_cache element.
- ANALYZE FORMAT=JSON should also show r_hits (how many times cache was invoked) and r_hit_ratio (0...100% - fraction of times there was a cache hit)
Attachments
Issue Links
- relates to
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-