Bug#21178196 : UPDATE MAY USE INDEX MERGE WITHOUT ANY REASON

Chaithra Gopalareddy · Chaithra Gopalareddy · commit 974f5a7c7dbd · 2016-02-25T10:21:36.000+05:30
(INCREASING CHANCES FOR DEADLOCK)

Problem:
Index merge access plan is choosen over range scan when the cost
for doing range scan would be same/less.

Analysis:
W.r.t the query presented in the bugpage, index merge access plan
is choosen because it is covering. However, while calculating the cost
for doing index merge scan, the current cost model thinks doing a sequential
scan is more costly than doing a random scan. For the given case,
while calculating the cost for disk sweep, we calculate cost for
reading the blocks having the qualifying rows and the cost for skipping
the other blocks. There is a minimal cost for skipping blocks too. And given
that in this case, we are looking at one qualifying row and rejecting 1590000
rows, we would end up skipping a lot of blocks. As a result the total cost
for doing sequential scan becomes larger than doing a random scan.

This in turn makes optimizer think doing index merge is better than the
other access plans.

Solution:
If it is calculated that sequential scan cost is more than random scan cost,
set the cost to random scan cost.

Also, set is_interrupted to true before calling get_sweep_read_cost
when the plan is not single table. This will make both single table select
and single table update choose the same code path. This is in line with
other calls made to get_sweep_read_cost.

Test case is not added as large amount of data is needed to get a repeatable
scenario.
diff --git a/mysql-test/suite/opt_trace/r/range_no_prot.result b/mysql-test/suite/opt_trace/r/range_no_prot.result
@@ -3442,8 +3442,8 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                           "index": "cola",
                           "index_scan_cost": 11.231,
                           "cumulated_index_scan_cost": 11.231,
-                          "disk_sweep_cost": 309.15,
-                          "cumulated_total_cost": 320.38,
+                          "disk_sweep_cost": 278.58,
+                          "cumulated_total_cost": 289.81,
                           "usable": true,
                           "matching_rows_now": 533,
                           "isect_covering_with_this_index": false,
@@ -3453,8 +3453,8 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                           "index": "colb",
                           "index_scan_cost": 11.231,
                           "cumulated_index_scan_cost": 22.462,
-                          "disk_sweep_cost": 30.898,
-                          "cumulated_total_cost": 53.359,
+                          "disk_sweep_cost": 28.152,
+                          "cumulated_total_cost": 50.613,
                           "usable": true,
                           "matching_rows_now": 32.639,
                           "isect_covering_with_this_index": false,
@@ -3466,7 +3466,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                         "cause": "no_clustered_pk_index"
                       } /* clustered_pk */,
                       "rows": 32,
-                      "cost": 53.359,
+                      "cost": 50.613,
                       "covering": false,
                       "chosen": true
                     } /* analyzing_roworder_intersect */
@@ -3475,7 +3475,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                     "range_access_plan": {
                       "type": "index_roworder_intersect",
                       "rows": 32,
-                      "cost": 53.359,
+                      "cost": 50.613,
                       "covering": false,
                       "clustered_pk_scan": false,
                       "intersect_of": [
@@ -3498,7 +3498,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                       ] /* intersect_of */
                     } /* range_access_plan */,
                     "rows_for_plan": 32,
-                    "cost_for_plan": 53.359,
+                    "cost_for_plan": 50.613,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -3530,12 +3530,12 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar'	{
                     {
                       "access_type": "range",
                       "rows": 24,
-                      "cost": 59.759,
+                      "cost": 57.013,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 59.759,
+                "cost_for_plan": 57.013,
                 "rows_for_plan": 24,
                 "chosen": true
               }
@@ -5519,8 +5519,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND i2 = 1  AND v = 'a' AND pk < 3	{
                           "index": "v_idx",
                           "index_scan_cost": 1,
                           "cumulated_index_scan_cost": 1,
-                          "disk_sweep_cost": 1,
-                          "cumulated_total_cost": 2,
+                          "disk_sweep_cost": 0.9031,
+                          "cumulated_total_cost": 1.9031,
                           "usable": true,
                           "matching_rows_now": 1,
                           "isect_covering_with_this_index": false,
diff --git a/sql/handler.cc b/sql/handler.cc
@@ -6642,13 +6642,30 @@ void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted,
 
     DBUG_PRINT("info",("sweep: nblocks=%g, busy_blocks=%g", n_blocks,
                        busy_blocks));
-    if (interrupted)
-      cost->add_io(busy_blocks * Cost_estimate::IO_BLOCK_READ_COST());
-    else
+    /*
+      The random access cost for reading the data pages will be the
+      upper limit for the sweep_cost.
+    */
+    cost->add_io(busy_blocks * Cost_estimate::IO_BLOCK_READ_COST());
+
+    if (!interrupted)
+    {
       /* Assume reading is done in one 'sweep' */
-      cost->add_io(busy_blocks * 
+      Cost_estimate sweep_cost;
+      sweep_cost.add_io(busy_blocks *
                    (DISK_SEEK_BASE_COST +
                     DISK_SEEK_PROP_COST * n_blocks / busy_blocks));
+      /*
+        For some cases, ex: when only few blocks need to be read
+        and the seek distance becomes very large, the sweep cost
+        model can produce a cost estimate that is larger than the
+        cost of random access.  To handle this case, we use the
+        sweep cost only when it is less than the random access
+        cost.
+      */
+      if (sweep_cost.get_io_cost() < cost->get_io_cost())
+        *cost= sweep_cost;
+    }
   }
   DBUG_PRINT("info",("returning cost=%g", cost->total_cost()));
   DBUG_VOID_RETURN;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights
+/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights
  * reserved.
 
    This program is free software; you can redistribute it and/or modify
@@ -5234,7 +5234,7 @@ static bool ror_intersect_add(ROR_INTERSECT_INFO *info,
   {
     Cost_estimate sweep_cost;
     JOIN *join= info->param->thd->lex->select_lex.join;
-    const bool is_interrupted= join && join->tables == 1;
+    const bool is_interrupted= join && join->tables != 1;
     get_sweep_read_cost(info->param->table, double2rows(info->out_rows),
                         is_interrupted, &sweep_cost);
     info->total_cost += sweep_cost.total_cost();
