[MDEV-30485] ANALYZE FORMAT=JSON: Show r_table_loops Created: 2023-01-27  Updated: 2023-10-30

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

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Oleg Smirnov
Resolution: Unresolved Votes: 0
Labels: analyze-stmt


 Description   

ANALYZE FORMAT=JSON shows r_loops. This is the number of times the control was passed to this table.

However, this is not always equal to the number of times the table access method was invoked.

Reasons:

  • Eq_ref lookup cache (studying it is the motivation for this MDEV)
  • Any error in join_read_always_key()/cp_buffer_from_ref(). The most common
    kind of error is that we've got a NULL for the lookup key. (we were doing a ref access on t2.key=t1.col and we've got NULL for t1.col).

This is already done for eq_ref in 11.0, but not for other access methods.

 Comments   
Comment by Sergei Petrunia [ 2023-01-27 ]

Implementation for eq_ref:

diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
 
index 5cfe3082b35..a885385610f 100644
 
--- a/sql/sql_explain.cc
 
+++ b/sql/sql_explain.cc
 
@@ -1922,7 +1922,15 @@ void Explain_table_access::print_explain_json(Explain_query *query,
 
   /* r_loops (not present in tabular output) */
 
   if (is_analyze)
 
   {
 
-    writer->add_member("r_loops").add_ll(tracker.get_loops());
 
+    ha_rows loops= tracker.get_loops();
 
+    writer->add_member("r_loops").add_ll(loops);
 
+    
+    if (type == JT_EQ_REF) // max one row
 
+    {
 
+      ha_rows table_loops= op_tracker.get_loops(); 
+      if (table_loops != loops)
 
+        writer->add_member("r_table_loops").add_ll(table_loops);
 
+    }
 
   }
 
   
   /* `rows` */

Comment by Sergei Petrunia [ 2023-01-27 ]

tracker counts number of times we've entered sub_select.

op_tracker counts how many times we did a read operation from the table. Since eq_ref access only does one read per each scan, this counter is good for counting lookups.

As for access methods that might read multiple rows, it seems we cannot produce this with current counters.

  • The number of table read operations is not usable because we potentially read many rows in some sub_select calls.
  • The number of rows before WHERE is not usable, because some lookups might not produce rows at all.
Comment by Sergei Petrunia [ 2023-03-06 ]

The following is in 11.0. It adds r_table_loops but ONLY for eq_ref access. (We don't need any extra counters in that case): 

commit 793caf3a27cf02bc3fc871558d79b490441103fe
 
Author: Monty <monty@mariadb.org>
 
Date:   Sun Feb 12 15:19:58 2023 +0200
 
 
 
    Added r_table_loops to "ANALYZE FORMAT=JSON statement"
 
    
    Author: Sergei Petrunia <sergey@mariadb.com>

Generated at Thu Feb 08 10:16:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.