maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

> revision-id: 500d909a795022255fb6f6c44cd310636afc0b5a (mariadb-10.3.6-226-g500d909)
> parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2019-03-11 20:20:35 +0530
> message:
> 
> MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
> 
> Keys with multi-key parts were not being printed correctly, only the first key part
> was getting printed.
> Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts.
>
...

> diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
> index 4c3e2b3..a4c2b86 100644
> --- a/mysql-test/main/opt_trace.result
> +++ b/mysql-test/main/opt_trace.result
> @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
>  set @@optimizer_switch= @save_optimizer_switch;
>  drop table t1,t2;
>  set optimizer_trace='enabled=off';
> +#
> +# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
> +#
> +create table t0(a int);
> +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
> +create table one_k (a int);
> +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
> +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d));
> +insert into t1 select a,a,a,a from one_k;
> +set optimizer_trace=1;
> +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1;
...
> +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe;

This produces a lot of irrelevant output. Can you use something like

  select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from optimizer_trace

to select on the part of the trace that we need?

> +                  "analyzing_range_alternatives": {
> +                    "range_scan_alternatives": [
> +                      {
> +                        "index": "a",
> +                        "ranges": ["10 < a AND NULL < b < 10"],

This is not a valid range. If one forces this quick select to be picked for the
query plan, they can look into .trace and see the ranges:

explain format=json select * from t1 force index (a) where a > 10 and b < 10;

T@19   : | | | | | | | | | | >print_quick
quick range select, key a, length: 5
  10 < X
other_keys: 0x0:
T@19   : | | | | | | | | | | <print_quick

(you can also try with a >=10 to see a different kind of range being produced).

(One could argue that what is printed is a representation of the SEL_ARG graph,
but in that case it is extremely confusing. I think, we should start with
showing ranges, not SEL_ARG graphs).

I think, we should not try to intrepret the SEL_ARG graph ourselves but rather 
use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges that can
be printed.  Any objections to this?

> +                        "rowid_ordered": false,
> +                        "using_mrr": false,
> +                        "index_only": false,
> +                        "rows": 989,
> +                        "cost": 1272.8,
> +                        "chosen": false,
> +                        "cause": "cost"
> +                      },
> +                      {
> +                        "index": "c",
> +                        "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"],
> +                        "rowid_ordered": true,
> +                        "using_mrr": false,
> +                        "index_only": false,
> +                        "rows": 1,
> +                        "cost": 2.3783,
> +                        "chosen": true
> +                      }
> +                    ],


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog