Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21626

Optimizer misses the details about the picked join order

    XMLWordPrintable

    Details

      Description

      Take the DBT-3 dataset, make sure you have these indexes:

      CREATE INDEX i_o_orderdate ON orders (o_orderdate);
      CREATE INDEX i_o_custkey ON orders (o_custkey);
      

      Run these two queries. The queries are the same, except for the ORDER BY list.
      However this difference causes different join orders to be picked:

      MariaDB [dbt3]> explain select * from orders,customer where o_custkey=c_custkey order by o_orderdate limit 10;
      +------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
      | id   | select_type | table    | type   | possible_keys | key           | key_len | ref                   | rows | Extra       |
      +------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
      |    1 | SIMPLE      | orders   | index  | i_o_custkey   | i_o_orderdate | 4       | NULL                  | 10   | Using where |
      |    1 | SIMPLE      | customer | eq_ref | PRIMARY       | PRIMARY       | 4       | dbt3.orders.o_custkey | 1    |             |
      +------+-------------+----------+--------+---------------+---------------+---------+-----------------------+------+-------------+
      

      MariaDB [dbt3]> explain select * from orders,customer where o_custkey=c_custkey order by c_acctbal limit 10; 
      +------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
      | id   | select_type | table    | type  | possible_keys | key         | key_len | ref                     | rows | Extra |
      +------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
      |    1 | SIMPLE      | customer | index | PRIMARY       | c_acctbal   | 9       | NULL                    | 1    |       |
      |    1 | SIMPLE      | orders   | ref   | i_o_custkey   | i_o_custkey | 5       | dbt3.customer.c_custkey | 15   |       |
      +------+-------------+----------+-------+---------------+-------------+---------+-------------------------+------+-------+
      

      However, looking at the optimizer trace, one can see that the join optimization parts are identical:

       MariaDB [dbt3]> select * from information_schema.optimizer_trace\G
       *************************** 1. row ***************************
      -                            QUERY: explain select * from orders,customer where o_custkey=c_custkey order by o_orderdate limit 10
      +                            QUERY: explain select * from orders,customer where o_custkey=c_custkey order by c_acctbal limit 10
                                   TRACE: {
         "steps": [
           {
      @@ -18,7 +17,7 @@
               "select_id": 1,
               "steps": [
                 {
      -            "expanded_query": ...
      +            "expanded_query": ...
                 } 
               ] 
             } 
      @@ -177,15 +176,15 @@
                 },
                 {
                   "attaching_conditions_to_tables": {
      -              "original_condition": "customer.c_custkey = orders.o_custkey",
      +              "original_condition": "orders.o_custkey = customer.c_custkey",
                     "attached_conditions_computation": [],
                     "attached_conditions_summary": [
                       {
      -                  "table": "orders",
      -                  "attached": "orders.o_custkey is not null"
      +                  "table": "customer",
      +                  "attached": null
                       },
      

      The first difference is in "attaching_conditions_to_tables" ?

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: