[MDEV-33054] ANALYZE FORMAT=JSON: rowid_filter.r_lookups should be average, not total Created: 2023-12-18  Updated: 2024-01-04

Status: In Progress
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: analyze-stmt, rowid_filtering

Issue Links:
Relates
relates to MDEV-18478 ANALYZE for statement should show sel... In Testing

 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.
Generated at Thu Feb 08 10:36:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.