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

Measure impact of optimizer_switch='exists_to_in=on' in 10.0

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      See https://mariadb.atlassian.net/browse/MDEV-38?focusedCommentId=28609&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-28609.

      I am looking at Q4.

      ts ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
      +------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
      | id   | select_type        | table    | type  | possible_keys                              | key           | key_len | ref                       | rows   | filtered | Extra                                                               |
      +------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
      |    1 | PRIMARY            | orders   | range | i_o_orderdate                              | i_o_orderdate | 4       | NULL                      | 137994 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
      |    2 | DEPENDENT SUBQUERY | lineitem | ref   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY       | 4       | dbt3sf1.orders.o_orderkey |      2 |   100.00 | Using where                                                         |
      +------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
      2 rows in set, 2 warnings (0.00 sec)
       
      MariaDB [dbt3sf1]> set optimizer_switch='exists_to_in=on';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [dbt3sf1]> explain extended select o_orderpriority, count(*) as order_count from orders where o_orderdate >= '1995-06-06' and o_orderdate < date_add('1995-06-06', interval 3 month) and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
      +------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
      | id   | select_type | table    | type  | possible_keys                              | key           | key_len | ref                       | rows   | filtered | Extra                                                  |
      +------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
      |    1 | PRIMARY     | orders   | range | PRIMARY,i_o_orderdate                      | i_o_orderdate | 4       | NULL                      | 137994 |   100.00 | Using index condition; Using temporary; Using filesort |
      |    1 | PRIMARY     | lineitem | ref   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY       | 4       | dbt3sf1.orders.o_orderkey |      2 |   100.00 | Using where; FirstMatch(orders)                        |
      +------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
      2 rows in set, 2 warnings (0.00 sec)

      The query plan is similar. However, the second query should be able to use BKA. Run the query with and without BKA and measure the impact.

      Attachments

        1. 04-opt1_explain.sql_1_results.txt
          5 kB
          Axel Schwenke
        2. 04-opt2_explain.sql_1_results.txt
          5 kB
          Axel Schwenke
        3. dbt3-q22.txt
          8 kB
          Sergei Petrunia
        4. q4-mrr-bka-real.png
          96 kB
          Sergei Petrunia
        5. q4-mrr-real-no-exists2in.png
          98 kB
          Sergei Petrunia
        6. q4-mrr-real-no-exists2in-io-only.png
          37 kB
          Sergei Petrunia

        Activity

          People

            axel Axel Schwenke
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.