[MDEV-30803] ANALYZE FORMAT=JSON, Join buffer: loops is in wrong position Created: 2023-03-07  Updated: 2023-03-07

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0
Fix Version/s: 11.0

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Check out this example:

create table t1 (a int);
 
insert into t1 select seq from seq_1_to_10;
 
 
 
create table t2 (a int);
 
insert into t2 select seq from seq_1_to_10;
 
 
 
analyze format=json 
select * 
from t1, t2
 
where t1.a=t2.a;

Shows

...
 
      {
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "t2",
 
            "access_type": "all",
 
            "loops": 10,
 
            "r_loops": 1,
 
            "rows": 10,
 
            "r_rows": 10,
 
            "cost": 0.02238006,
 
            "r_table_time_ms": 0.060573394,
 
            "r_other_time_ms": 0.00925612,
 
            "filtered": 100,
 
            "r_filtered": 100
 
          },
 
          "buffer_type": "flat",
 
          "buffer_size": "65",
 
          "join_type": "BNL",
 
          "attached_condition": "t2.a = t1.a",
 
          "r_filtered": 10,
 
          "r_unpack_time_ms": 0.004839758
 
        }

This part looks very misleading:

          "table": {
 
            "table_name": "t2",
 
            "access_type": "all",
 
            "loops": 10,
 
            "r_loops": 1,

We do not expect table t2 to be read 10 times.
"loops" should outside of table object, in block-nl-join.
Generated at Thu Feb 08 10:19:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.