Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6
Description
Consider this example from mysql-test/main/rowid_filter.result:
set statement optimizer_switch='rowid_filter=on' for |
ANALYZE FORMAT=JSON
|
SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice |
FROM orders JOIN lineitem ON o_orderkey=l_orderkey |
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND |
o_totalprice between 200000 and 230000; |
{
|
...
|
"nested_loop": [ |
{
|
"table": { |
"table_name": "orders", |
...
|
}
|
},
|
{
|
"table": { |
"table_name": "lineitem", |
"access_type": "ref", |
"key": "i_l_orderkey", |
.
"rowid_filter": { |
"range": { |
"key": "i_l_shipdate", |
"used_key_parts": ["l_shipDATE"] |
},
|
"rows": 98, |
"selectivity_pct": 1.631973356, |
"r_rows": 98, |
"r_lookups": 476, |
"r_selectivity_pct": 2.31092437, |
"r_buffer_size": "REPLACED", |
"r_filling_time_ms": "REPLACED" |
},
|
"loops": 69, |
"r_loops": 71, |
"rows": 4, |
"r_rows": 0.154929577, |
"cost": "REPLACED", |
"r_table_time_ms": "REPLACED", |
"r_other_time_ms": "REPLACED", |
"r_engine_stats": REPLACED, |
}
|
}
|
There are r_loops=71 lookups made for table lineitem.
On each lookup, on average r_rows=0.154929577 rows are returned to the SQL layer.
Rowid filter has r_selectivity_pct=2.31092437% , this means in total there were 71 * 0.154929577 / 0.0231092437 = 476 lookups made.
This agrees with rowid_filter.r_lookups=476.
However, the average per-scan number of rowid_filter.r_lookups would have been more useful. r_lookups=476 is not directly comparable to anything.
The per-scan average of 476/71= 6.7 rows is comparable: we see the estimate table.rows=4. We've got 6.7 on average and rowid filter has reduced it to 0.154929577.
This MDEV is about making rowid_filter.r_lookups to be per-scan average.
Attachments
Issue Links
- relates to
-
MDEV-18478 ANALYZE for statement should show selectivity of pushed index condition
-
- Closed
-