[Commits] Rev 3502: MDEV-4170 Dynamic pushdown of subquery predicates in file:///home/tsk/mprog/src/10.0-md4170-dyn-push/

timour at askmonty.org timour at askmonty.org
Thu Feb 21 17:54:01 EET 2013


At file:///home/tsk/mprog/src/10.0-md4170-dyn-push/

------------------------------------------------------------
revno: 3502
revision-id: timour at askmonty.org-20130221155346-g1nblui6o0vpg78e
parent: sanja at askmonty.org-20130204153039-ws7tcwfmbi6vil6e
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4170
committer: timour at askmonty.org
branch nick: 10.0-md4170-dyn-push
timestamp: Thu 2013-02-21 17:53:46 +0200
message:
  MDEV-4170  Dynamic pushdown of subquery predicates 
  
  Intermediate commit.
  
  This patch implements a wrapper of conditions that allows
  conditions to be dynamically "moved" to different JOIN_TABs.
  In this patch the conditions are not moved, they stay where
  the optimizer put them initally, but the patch tests the
  mechanism of wrapping and enabling a dynamic condition for
  its original JOIN_TAB. All tests pass.
-------------- next part --------------
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2013-01-29 14:10:47 +0000
+++ b/sql/item_cmpfunc.cc	2013-02-21 15:53:46 +0000
@@ -6165,3 +6165,9 @@ longlong Item_func_dyncol_exists::val_in
   null_value= TRUE;
   return 0;
 }
+
+
+longlong Item_func_dynamic_cond::val_int()
+{
+  return (!(*cur_tab) || active_tab == *cur_tab) ? args[0]->val_int() : 1;
+}

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-01-29 14:10:47 +0000
+++ b/sql/item_cmpfunc.h	2013-02-21 15:53:46 +0000
@@ -489,6 +489,30 @@ class Item_func_trig_cond: public Item_b
   bool *get_trig_var() { return trig_var; }
 };
 
+/**
+*/
+class Item_func_dynamic_cond : public Item_bool_func
+{
+protected:
+  /* The join_tab where the condition is currently 'pushed'.*/
+  struct st_join_table *active_tab;
+  /* The join_tab currently being executed. */
+  struct st_join_table **cur_tab;
+public:
+Item_func_dynamic_cond(Item *cond, struct st_join_table *atab,
+                       struct st_join_table **ctab) :
+  Item_bool_func(cond)
+  {
+    active_tab= atab;
+    cur_tab= ctab;
+  }
+  longlong val_int();
+  enum Functype functype() const { return DYNAMIC_COND_FUNC; };
+  const char *func_name() const { return "dyncond"; };
+  bool const_item() const { return FALSE; }
+  void move_cond(struct st_join_table *new_tab) { active_tab= new_tab; }
+};
+
 class Item_func_not_all :public Item_func_not
 {
   /* allow to check presence of values in max/min optimization */

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2013-01-29 14:10:47 +0000
+++ b/sql/item_func.h	2013-02-21 15:53:46 +0000
@@ -57,7 +57,7 @@ class Item_func :public Item_result_fiel
                   SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING,
                   SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN,
                   NOT_FUNC, NOT_ALL_FUNC,
-                  NOW_FUNC, TRIG_COND_FUNC,
+                  NOW_FUNC, TRIG_COND_FUNC, DYNAMIC_COND_FUNC,
                   SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC,
                   EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC,
                   NEG_FUNC, GSYSVAR_FUNC, DYNCOL_FUNC };

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-01-31 08:48:19 +0000
+++ b/sql/item_subselect.cc	2013-02-21 15:53:46 +0000
@@ -53,9 +53,7 @@ double get_post_group_estimate(JOIN* joi
 {
   DBUG_ENTER("Item_subselect::Item_subselect");
   DBUG_PRINT("enter", ("this: 0x%lx", (ulong) this));
-#ifndef DBUG_OFF
-  exec_counter= 0;
-#endif
+  exec_counter= true_counter= 0;
   with_subselect= 1;
   reset();
   /*
@@ -143,9 +141,8 @@ void Item_subselect::cleanup()
   expr_cache= 0;
   forced_const= FALSE;
   DBUG_PRINT("info", ("exec_counter: %d", exec_counter));
-#ifndef DBUG_OFF
-  exec_counter= 0;
-#endif
+  DBUG_PRINT("info", ("true_counter: %d", true_counter));
+  exec_counter= true_counter= 0;
   DBUG_VOID_RETURN;
 }
 
@@ -643,9 +640,7 @@ bool Item_subselect::exec()
 
   bool res= engine->exec();
 
-#ifndef DBUG_OFF
   ++exec_counter;
-#endif
   if (engine != org_engine)
   {
     /*

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2012-10-25 12:50:10 +0000
+++ b/sql/item_subselect.h	2013-02-21 15:53:46 +0000
@@ -73,10 +73,9 @@ class Item_subselect :public Item_result
     to substitute 'this' with a constant item.
   */
   bool forced_const;
-#ifndef DBUG_OFF
   /* Count the number of times this subquery predicate has been executed. */
   uint exec_counter;
-#endif
+  uint true_counter;
 public:
   /* 
     Used inside Item_subselect::fix_fields() according to this scenario:

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2013-01-31 08:48:19 +0000
+++ b/sql/sql_join_cache.cc	2013-02-21 15:53:46 +0000
@@ -2080,6 +2080,7 @@ enum_nested_loop_state JOIN_CACHE::join_
   if (outer_join_first_inner && !join_tab->first_unmatched)
     join_tab->not_null_compl= TRUE;   
 
+  *(join->cur_exec_tab)= join_tab;
   if (!join_tab->first_unmatched)
   {
     /* Find all records from join_tab that match records from join buffer */

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-01-31 08:48:19 +0000
+++ b/sql/sql_select.cc	2013-02-21 15:53:46 +0000
@@ -1449,6 +1449,12 @@ JOIN::optimize_inner()
     goto setup_subq_exit;
   }
 
+  if (add_dynamic_conditions())
+  {
+    error= 1;
+    DBUG_RETURN(1);
+  }
+
   error= -1;                                    /* if goto err */
 
   /* Optimize distinct away if possible */
@@ -3298,6 +3304,7 @@ make_join_statistics(JOIN *join, List<TA
   */
   join->best_positions= (POSITION*) join->thd->alloc(sizeof(POSITION)*
                                                      (table_count +1));
+  join->cur_exec_tab= (JOIN_TAB**) join->thd->calloc(sizeof(JOIN_TAB*));
 
   if (join->thd->is_fatal_error)
     DBUG_RETURN(1);                             // Eom /* purecov: inspected */
@@ -8272,6 +8278,7 @@ JOIN::make_simple_join(JOIN *parent, TAB
   bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
   temp_table->status=0;
   temp_table->null_row=0;
+  *cur_exec_tab= *(parent->cur_exec_tab);
   DBUG_RETURN(FALSE);
 }
 
@@ -16349,6 +16356,8 @@ evaluate_join_record(JOIN *join, JOIN_TA
   if (join_tab->table->vfield)
     update_virtual_fields(join->thd, join_tab->table);
 
+  *(join->cur_exec_tab)= join_tab;
+
   if (select_cond)
   {
     select_cond_result= test(select_cond->val_int());
@@ -16527,6 +16535,8 @@ evaluate_null_complemented_join_record(J
     and no matches has been found for the current outer row.
   */
   JOIN_TAB *last_inner_tab= join_tab->last_inner;
+  *(join->cur_exec_tab)= join_tab;
+
   /* Cache variables for faster loop */
   COND *select_cond;
   for ( ; join_tab <= last_inner_tab ; join_tab++)
@@ -23051,6 +23061,76 @@ void JOIN::cache_const_exprs()
   }
 }
 
+/**
+  Make expensive subqueries dynamically pushdownable.
+*/
+
+bool JOIN::add_dynamic_conditions()
+{
+  List<Item_func_dynamic_cond> dynamic_conds;
+  JOIN_TAB *first_tab, *tab;
+
+  first_tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
+  for (tab= first_tab; tab;
+       tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))
+  {
+    Item_func_dynamic_cond *dyn_cond;
+
+    if (!tab->select_cond)
+      continue;
+    if (tab->bush_root_tab)
+      continue; /* TODO: how to process nested tabs?*/
+
+    if (!(tab->select_cond->with_subselect && tab->select_cond->is_expensive() &&
+          !tab->select_cond->const_item()))
+      continue;
+
+    /* Add all previously created dynamic conditions to the current join tab.*/
+    List_iterator_fast<Item_func_dynamic_cond> dyn_cond_it(dynamic_conds);
+    while ((dyn_cond= dyn_cond_it++))
+    {
+      if (is_cond_and(tab->select_cond))
+        ((Item_cond_and*)tab->select_cond)->add(dyn_cond);
+      else
+        tab->set_select_cond(and_items(tab->select_cond, dyn_cond), __LINE__);
+    }
+
+    /*
+      Wrap the AND-parts of the current push down conditions in a dynamically
+      triggered condition.
+    */
+    if (is_cond_and(tab->select_cond))
+    {
+      List_iterator<Item> and_parts_it(*((Item_cond*) tab->select_cond)->argument_list());
+      Item *and_part;
+      while ((and_part= and_parts_it++))
+      {
+        if (!(and_part->with_subselect && and_part->is_expensive() &&
+              !and_part->const_item()))
+          continue;
+        if (!(dyn_cond= new Item_func_dynamic_cond(and_part, tab,
+                                                   cur_exec_tab)))
+          return true;
+        and_parts_it.replace(dyn_cond);
+        dynamic_conds.push_back(dyn_cond);
+      }
+    }
+    else
+    {
+      if (!(dyn_cond= new Item_func_dynamic_cond(tab->select_cond, tab,
+                                                 cur_exec_tab)))
+        return true;
+      tab->set_select_cond(dyn_cond, __LINE__);
+      dynamic_conds.push_back(dyn_cond);
+    }
+
+    tab->select_cond->quick_fix_field();
+    tab->select_cond->update_used_tables();
+  }
+
+  return false;
+}
+
 
 /**
   Find a cheaper access key than a given @a key

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-12-18 14:01:58 +0000
+++ b/sql/sql_select.h	2013-02-21 15:53:46 +0000
@@ -288,6 +288,14 @@ typedef struct st_join_table {
     
   double        partial_join_cardinality;
 
+  /* Execution-time counters to estimate actual cardinality and selectivity. */
+  /* The number of times select_cond was executed. */
+  ha_rows select_cond_val_counter;
+  /* The number of times select_cond was TRUE. */
+  ha_rows select_cond_true_counter;
+  /* The cardinality of the partial join represented by this JOIN_TAB. */
+  ha_rows partial_join_result_counter;
+
   table_map     dependent,key_dependent;
   /*
      1 - use quick select
@@ -1219,6 +1227,11 @@ class JOIN :public Sql_alloc
   JOIN_TAB *join_tab_reexec;                    // make_simple_join()
   /* end of allocation caching storage */
 
+  /*
+    The index of the current partial join being processed during query execution.
+  */
+  JOIN_TAB **cur_exec_tab;
+
   JOIN(THD *thd_arg, List<Item> &fields_arg, ulonglong select_options_arg,
        select_result *result_arg)
     :fields_list(fields_arg)
@@ -1297,6 +1310,7 @@ class JOIN :public Sql_alloc
     in_to_exists_having= NULL;
 
     pre_sort_join_tab= NULL;
+    cur_exec_tab= NULL;
   }
 
   int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num,
@@ -1418,6 +1432,8 @@ class JOIN :public Sql_alloc
   double get_examined_rows();
   /* defined in opt_subselect.cc */
   bool transform_max_min_subquery();
+  bool add_dynamic_conditions();
+
   /* True if this JOIN is a subquery under an IN predicate. */
   bool is_in_subquery()
   {



More information about the commits mailing list