List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:March 15 2011 2:51pm
Subject:Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)
View as plain text  
Jorgen Loland a écrit, Le 15.03.2011 14:31:
> #At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/
> based on revid:guilhem.bichot@stripped
> 
>  3279 Jorgen Loland	2011-03-15
>       Added test case for multiple ranges over a multi-keypart range
>       
>       Trace failed to print all but the final range in the 
>       chosen_range_access_summary{} section of the optimizer trace. 
>       Fixed by navigating to the first range interval before printing 
>       ranges.
> 
>     modified:
>       mysql-test/include/optimizer_trace_range.inc
>       mysql-test/r/optimizer_trace_range_no_prot.result
>       mysql-test/r/optimizer_trace_range_ps_prot.result
>       sql/opt_range.cc

I applied the patch and then applied opt_range.cc, to see what the 
problem was:

> === modified file 'mysql-test/r/optimizer_trace_range_no_prot.result'
> --- a/mysql-test/r/optimizer_trace_range_no_prot.result	2011-03-01 10:55:27 +0000
> +++ b/mysql-test/r/optimizer_trace_range_no_prot.result	2011-03-15 13:31:03 +0000
> @@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
>    ] /* steps */
>  }	0
>  
> +EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
> +(key1a = 4 and key1b < 7 and key1b > 3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	t2	range	PRIMARY,i1b	PRIMARY	8	NULL	2	Using index condition
> +
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
> +EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
> +(key1a = 4 and key1b < 7 and key1b > 3)	{
> +  "steps": [
> +    {
> +      "join_preparation": {
> +        "select#": 1,
> +        "steps": [
> +          {
> +            "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS
> `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1`
> AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2`
> where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` <
> 7) and (`test`.`t2`.`key1b` > 3)))"
> +          }
> +        ] /* steps */
> +      } /* join_preparation */
> +    },
> +    {
> +      "join_optimization": {
> +        "select#": 1,
> +        "steps": [
> +          {
> +            "condition_processing": {
> +              "condition": "WHERE",
> +              "original_condition": "(((`test`.`t2`.`key1a` = 5) and
> (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` =
> 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
> +              "steps": [
> +                {
> +                  "transformation": "equality_propagation",
> +                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> +                },
> +                {
> +                  "transformation": "constant_propagation",
> +                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> +                },
> +                {
> +                  "transformation": "trivial_condition_removal",
> +                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> +                }
> +              ] /* steps */
> +            } /* condition_processing */
> +          },
> +          {
> +            "ref_optimizer_key_uses": [
> +            ] /* ref_optimizer_key_uses */
> +          },
> +          {
> +            "records_estimation": [
> +              {
> +                "database": "test",
> +                "table": "t2",
> +                "range_analysis": {
> +                  "table_scan": {
> +                    "records": 1024,
> +                    "cost": 215.15
> +                  } /* table_scan */,
> +                  "potential_range_indices": [
> +                    {
> +                      "index": "PRIMARY",
> +                      "usable": true,
> +                      "key_parts": [
> +                        "key1a",
> +                        "key1b"
> +                      ] /* key_parts */
> +                    },
> +                    {
> +                      "index": "i1b",
> +                      "usable": true,
> +                      "key_parts": [
> +                        "key1b",
> +                        "key1a"
> +                      ] /* key_parts */
> +                    },
> +                    {
> +                      "index": "i2_1",
> +                      "usable": false,
> +                      "cause": "not_applicable"
> +                    },
> +                    {
> +                      "index": "i2_2",
> +                      "usable": false,
> +                      "cause": "not_applicable"
> +                    }
> +                  ] /* potential_range_indices */,
> +                  "setup_range_conditions": [
> +                  ] /* setup_range_conditions */,
> +                  "group_index_range": {
> +                    "chosen": false,
> +                    "cause": "not_group_by_or_distinct"
> +                  } /* group_index_range */,
> +                  "analyzing_range_alternatives": {
> +                    "range_scan_alternatives": [
> +                      {
> +                        "index": "PRIMARY",
> +                        "ranges": [
> +                          "4 <= key1a <= 4 AND 3 < key1b < 7",
> +                          "5 <= key1a <= 5 AND 2 < key1b < 10"

those two ranges are printed

> +                        ] /* ranges */,
> +                        "index_only": false,
> +                        "records": 2,
> +                        "cost": 4.41,
> +                        "rowid_ordered": false,
> +                        "chosen": true
> +                      },
> +                      {
> +                        "index": "i1b",
> +                        "ranges": [
> +                          "2 < key1b <= 3 AND 5 <= key1a <= 5",
> +                          "3 < key1b < 7",
> +                          "7 <= key1b < 10 AND 5 <= key1a <= 5"

those three too

> +                        ] /* ranges */,
> +                        "index_only": false,
> +                        "records": 6,
> +                        "cost": 10.21,
> +                        "rowid_ordered": false,
> +                        "chosen": false,
> +                        "cause": "cost"
> +                      }
> +                    ] /* range_scan_alternatives */,
> +                    "analyzing_roworder_intersect": {
> +                      "usable": false,
> +                      "cause": "too_few_roworder_scans"
> +                    } /* analyzing_roworder_intersect */
> +                  } /* analyzing_range_alternatives */,
> +                  "chosen_range_access_summary": {
> +                    "range_access_plan": {
> +                      "type": "range_scan",
> +                      "index": "PRIMARY",
> +                      "records": 2,
> +                      "ranges": [
> +                        "4 <= key1a <= 4 AND 3 < key1b < 7",

this one above is not printed (as indicated in the commit comment).

In sel_arg_range_seq_next(), we seem to navigate:
walk_up_n_right:
     while (key_tree->prev && key_tree->prev != &null_element)
     {

don't you need such "&& key_tree->prev != &null_element" in your 
navigation too?

> +                        "5 <= key1a <= 5 AND 2 < key1b < 10"
> +                      ] /* ranges */
> +                    } /* range_access_plan */,
> +                    "records_for_plan": 2,
> +                    "cost_for_plan": 4.41,
> +                    "chosen": true
> +                  } /* chosen_range_access_summary */
> +                } /* range_analysis */
> +              }
> +            ] /* records_estimation */
> +          },
> +          {
> +            "considered_execution_plans": [
> +              {
> +                "database": "test",
> +                "table": "t2",
> +                "best_access_path": {
> +                  "considered_access_paths": [
> +                    {
> +                      "access_type": "range",
> +                      "records": 2,
> +                      "cost": 4.41,
> +                      "chosen": true
> +                    }
> +                  ] /* considered_access_paths */
> +                } /* best_access_path */,
> +                "cost_for_plan": 4.41,
> +                "records_for_plan": 2,
> +                "chosen": true
> +              }
> +            ] /* considered_execution_plans */
> +          },
> +          {
> +            "attaching_conditions_to_tables": {
> +              "original_condition": "(((`test`.`t2`.`key1a` = 5) and
> (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` =
> 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
> +              "attached_conditions_computation": [
> +              ] /* attached_conditions_computation */,
> +              "attached_conditions_summary": [
> +                {
> +                  "database": "test",
> +                  "table": "t2",
> +                  "attached": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b`
> < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and
> (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
> +                }
> +              ] /* attached_conditions_summary */
> +            } /* attaching_conditions_to_tables */
> +          },
> +          {
> +            "refine_plan": [
> +              {
> +                "database": "test",
> +                "table": "t2",
> +                "scan_type": "table"
> +              }
> +            ] /* refine_plan */
> +          }
> +        ] /* steps */
> +      } /* join_optimization */
> +    },
> +    {
> +      "join_execution": {
> +        "select#": 1,
> +        "steps": [
> +        ] /* steps */
> +      } /* join_execution */
> +    }
> +  ] /* steps */
> +}	0
> +
>  EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2  > 2);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	SIMPLE	t1	ALL	i1,i2	NULL	NULL	NULL	1024	Using where
> 

> === modified file 'sql/opt_range.cc'
> --- a/sql/opt_range.cc	2011-02-16 15:06:06 +0000
> +++ b/sql/opt_range.cc	2011-03-15 13:31:03 +0000
> @@ -2018,13 +2018,17 @@ void TRP_RANGE::trace_basic_info(const P
>      add_utf8("index", cur_key.name).add("records", records);
>  
>    Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
> -  for (const SEL_ARG *current= key;
> -       current;
> -       current= current->next)
> +
> +  const SEL_ARG *current_range= key;

do you understand why TRP_RANGE::key is the _last_ of ranges?

In the testcase (added to the .inc file), TRP_RANGE::key is set here 
(opt_range.cc:5367):

   if (key_to_read)
   {
     idx= key_to_read - tree->keys;
     if ((read_plan= new (param->mem_root) TRP_RANGE(*key_to_read, idx,
                                                     best_mrr_flags)))
(the constructor sets TRP_RANGE::key to *key_to_read).
Is there, in the existing code, some place where we take this 
TRP_RANGE::key and go up following the "prev" pointers?
Without an existing example, I'm unsure of what we're doing... I'm 
unsure of what the _execution_ really searches for in the table:
both
                         "4 <= key1a <= 4 AND 3 < key1b < 7",
                         "5 <= key1a <= 5 AND 2 < key1b < 10"
or just
"5 <= key1a <= 5 AND 2 < key1b < 10"
??

> +  // make current_range point to the first interval
> +  while (current_range->prev)

is it 100% sure that current_range starts non-NULL (so that we can read 
its "prev" above)?

> +    current_range= current_range->prev;
> +
> +  while (current_range)

in other existing code I find:
     /* Step down if we can */
     if (key_tree->next && key_tree->next != &null_element)
     {
       // Step down; update the tuple

so don't we need "&& current_range != &null_element" in the above 
while() condition?

>    {
>      String range_info;
>      range_info.set_charset(system_charset_info);
> -    for (const SEL_ARG *part= current;
> +    for (const SEL_ARG *part= current_range;
>           part;
>           part= part->next_key_part)
>      {
> @@ -2034,6 +2038,7 @@ void TRP_RANGE::trace_basic_info(const P
>                     part->min_flag | part->max_flag);
>      }
>      trace_range.add_utf8(range_info.ptr(), range_info.length());
> +    current_range= current_range->next;
>    }
>  #endif
>  }

Thread
bzr commit into mysql-trunk branch (jorgen.loland:3279) Jorgen Loland15 Mar
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Guilhem Bichot15 Mar
    • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Jorgen Loland16 Mar
      • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Jorgen Loland16 Mar
      • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Guilhem Bichot18 Mar