List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:October 15 2010 6:45pm
Subject:one trace per row scanned, by range optimizer
View as plain text  
Hello,

I'm attaching a result file for discussion.
I got it by using your patch and disabling the assertion in 
Opt_trace_context::syntax_error(), so that the test can proceed further.
There is a first problem in "attached_conditions", but easy to solve and 
I have just pushed a fix for it (revno 3221). The more interesting 
happens at end of object "join_optimization" (look for the "invalid 
JSON" words around there).

For such query:

select * from t1, t2
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;

we can see that the range optimizer is called once per row of t1: we 
read a row of t1, and we call the range optimizer with the range "2 days 
before or after this row of t1". This is the so-called "range checked 
for each record" (visible in EXPLAIN). Two rows in t1, so two pieces of 
trace related to the range optimizer.
In my test there are only two outer rows, but imagine if there would be 
1000 outer rows: we would have 1000 traces of the range optimizer.
What is the right approach?
1) Try to disable tracing at the right moment, to avoid this?
2) Or let this tracing be, saying that the range optimizer, even called 
1000 times in the execution phase, is still an important optimization 
information, and just count on truncation by 
@@optimizer_trace_max_mem_size (default: 16kB) if things go unreasonably 
big? That doesn't sound too bad to me. If we go this way, I should 
define a proper container for those range-opt-at-exec-time traces: a 
"join_execution" object in the trace seems mandated, with a "steps" 
array underneath; each range optimizer trace becomes an element of the 
array. There is another problem: traces generated by execution have the 
potential to be big, so truncation by max_mem_size would become 
frequent; a truncated trace is not parsable by a JSON parser. So the 
only way for a user to get a parsable trace is to increase max_mem_size 
until it's big enough to host the execution trace, and it may be 
unreasonably big then. So maybe we need a flag in JOIN::exec()'s top 
object ("EXECUTION"?), so that the user can request no tracing in 
JOIN::exec() and its children. Then the trace stays small, below 
max_mem_size, not truncated, and parsable.

Disabling tracing at the start of JOIN::exec() is not a solution: in 
other queries (like the first one using a subquery, in 
optimizer_trace_no_prot.test), JOIN::optimize for the subquery is called 
by the top query's JOIN::exec... so if I disable tracing of 
JOIN::exec(), I lose tracing of the subquery's JOIN::optimize().

Do you have an opinion?

-- 
Mr. Guilhem Bichot <guilhem.bichot@stripped>
Oracle / MySQL / Optimizer team, Lead Software Engineer
Bordeaux, France
www.oracle.com / www.mysql.com

create table t1(a date);
create table t2(a date, key(a));
insert into t1 values('2005-10-01'),('2010-10-12');
insert into t2 values('2005-10-02'),('2010-10-13');
set optimizer_trace="enabled=on,end_marker=on";
select * from t1, t2
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
a	a
2005-10-01	2005-10-02
2010-10-12	2010-10-13
SELECT trace FROM information_schema.optimizer_trace;
trace
{
  "top_query": "select * from t1, t2\nwhere t2.a between t1.a - interval 2 day and t1.a +
interval 2 day",
  "steps": [
    {
      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` join
`test`.`t2` where (`t2`.`a` between (`t1`.`a` - interval 2 day) and (`t1`.`a` + interval
2 day))"
    },
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval
2 day) and (`test`.`t1`.`a` + interval 2 day))",
              "after_equality_propagation": "(`test`.`t2`.`a` between (`test`.`t1`.`a` -
interval 2 day) and (`test`.`t1`.`a` + interval 2 day))",
              "after_constant_propagation": "(`test`.`t2`.`a` between (`test`.`t1`.`a` -
interval 2 day) and (`test`.`t1`.`a` + interval 2 day))",
              "after_trivial_conditions_removal": "(`test`.`t2`.`a` between
(`test`.`t1`.`a` - interval 2 day) and (`test`.`t1`.`a` + interval 2 day))"
            } /* condition_processing */
          },
          {
            "ref-optimizer-key-uses": [
            ] /* ref-optimizer-key-uses */
          },
          {
            "constant_tables": [
            ] /* constant_tables */,
            "records_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "records": 2,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "t2",
                "table_scan": {
                  "records": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* records_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "using_range_access": false,
                      "using_join_cache": true,
                      "with_where_cond_processing": {
                        "records": 2,
                        "cost": 2.0034
                      } /* with_where_cond_processing */,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.0034,
                "records_for_plan": 2,
                "rest_of_plan": [
                  {
                    "table": "t2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "using_range_access": false,
                          "using_join_cache": true,
                          "with_where_cond_processing": {
                            "records": 2,
                            "cost": 2.0035
                          } /* with_where_cond_processing */,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 4.007,
                    "records_for_plan": 4,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              },
              {
                "table": "t2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "using_range_access": false,
                      "using_join_cache": true,
                      "with_where_cond_processing": {
                        "records": 2,
                        "cost": 2.0034
                      } /* with_where_cond_processing */,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.0034,
                "records_for_plan": 2,
                "pruned_by_heuristic": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval
2 day) and (`test`.`t1`.`a` + interval 2 day))",
              "attached_conditions": [
                {
                  "table": "t1",
                  "attached": null
                },
                ** invalid JSON (unexpected key "range_analysis") ** {
                  "table_scan": {
                    "records": 2,
                    "cost": 4.5034
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "a",
                      "key_parts": [
                        "a"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "best_full_index_scan": {
                    "index": "a",
                    "cost": 1.4213,
                    "chosen": true
                  } /* best_full_index_scan */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a",
                        "index_only": true,
                        "records": 18446744073709551615,
                        "cost": 0,
                        "rowid_ordered": false,
                        "chosen": false,
                        "cause": "higher_cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_ror_intersect": {
                      "usable": false,
                      "cause": "too_few_ror_scans"
                    } /* analyzing_ror_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "chosen": false
                  } /* chosen_range_access_summary */,
                  "records": 2
                } /* range_analysis */,
                {
                  "table": "t2",
                  "attached": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval 2 day)
and (`test`.`t1`.`a` + interval 2 day))"
                }
              ] /* attached_conditions */
            } /* attaching_conditions_to_tables */
          }
        ] /* steps */
      } /* join_optimization */
    },
    ** invalid JSON (unexpected key "range_analysis") ** {
      "table_scan": {
        "records": 2,
        "cost": 4.5034
      } /* table_scan */,
      "potential_range_indices": [
        {
          "index": "a",
          "key_parts": [
            "a"
          ] /* key_parts */
        }
      ] /* potential_range_indices */,
      "best_full_index_scan": {
        "index": "a",
        "cost": 1.4213,
        "chosen": true
      } /* best_full_index_scan */,
      "group_index_range": {
        "chosen": false,
        "cause": "not_single_table"
      } /* group_index_range */,
      "analyzing_range_alternatives": {
        "range_scan_alternatives": [
          {
            "index": "a",
            "ranges": [
              "2005-09-29 <= a <= 2005-10-03"
            ] /* ranges */,
            "index_only": true,
            "records": 1,
            "cost": 2.21,
            "rowid_ordered": false,
            "chosen": false,
            "cause": "higher_cost"
          }
        ] /* range_scan_alternatives */,
        "analyzing_ror_intersect": {
          "usable": false,
          "cause": "too_few_ror_scans"
        } /* analyzing_ror_intersect */
      } /* analyzing_range_alternatives */,
      "chosen_range_access_summary": {
        "chosen": false
      } /* chosen_range_access_summary */,
      "records": 2
    } /* range_analysis */,
    ** invalid JSON (unexpected key "range_analysis") ** {
      "table_scan": {
        "records": 2,
        "cost": 4.5034
      } /* table_scan */,
      "potential_range_indices": [
        {
          "index": "a",
          "key_parts": [
            "a"
          ] /* key_parts */
        }
      ] /* potential_range_indices */,
      "best_full_index_scan": {
        "index": "a",
        "cost": 1.4213,
        "chosen": true
      } /* best_full_index_scan */,
      "group_index_range": {
        "chosen": false,
        "cause": "not_single_table"
      } /* group_index_range */,
      "analyzing_range_alternatives": {
        "range_scan_alternatives": [
          {
            "index": "a",
            "ranges": [
              "2010-10-10 <= a <= 2010-10-14"
            ] /* ranges */,
            "index_only": true,
            "records": 1,
            "cost": 2.21,
            "rowid_ordered": false,
            "chosen": false,
            "cause": "higher_cost"
          }
        ] /* range_scan_alternatives */,
        "analyzing_ror_intersect": {
          "usable": false,
          "cause": "too_few_ror_scans"
        } /* analyzing_ror_intersect */
      } /* analyzing_range_alternatives */,
      "chosen_range_access_summary": {
        "chosen": false
      } /* chosen_range_access_summary */,
      "records": 2
    } /* range_analysis */
  ] /* steps */
}

Thread
one trace per row scanned, by range optimizerGuilhem Bichot15 Oct
  • Re: one trace per row scanned, by range optimizerGuilhem Bichot16 Oct
    • Re: one trace per row scanned, by range optimizerJorgen Loland18 Oct
      • Re: one trace per row scanned, by range optimizerGuilhem Bichot21 Oct
        • Re: one trace per row scanned, by range optimizerJorgen Loland21 Oct
          • Re: one trace per row scanned, by range optimizerGuilhem Bichot22 Oct