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

TPC-C query regression using 'derived_merge=on'

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.1.22, 10.2
    • 5.5.57
    • Optimizer
    • None

    Description

      explain select count(*) from(   SELECT   s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8  )) as L;
      +------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      | id   | select_type  | table            | type  | possible_keys | key     | key_len | ref         | rows   | Extra       |
      +------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      |    1 | PRIMARY      | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       | 187638 | Using where |
      |    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |      1 |             |
      |    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    191 | Using where |
      +------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      

      if only running the inner query it gives without outer count

      SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8         )   ;
      +--------+--------+------------+
      | s_w_id | s_i_id | s_quantity |
      +--------+--------+------------+
      |      3 |  16009 |         14 |
      |      3 |  16185 |         15 |
      |      3 |  26154 |         12 |
      |      3 |  36457 |         12 |
      |      3 |  36490 |         15 |
      |      3 |  42634 |         12 |
      |      3 |  55333 |         13 |
      |      3 |  76808 |         14 |
      |      3 |  76918 |         13 |
      |      3 |  76937 |         15 |
      +--------+--------+------------+
      10 rows in set (0.00 sec)
      

      now i just count this and the plan

      [+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      | id   | select_type  | table            | type  | possible_keys | key     | key_len | ref         | rows   | Extra       |
      +------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      |    1 | PRIMARY      | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       | 187638 | Using where |
      |    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |      1 |             |
      |    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    191 | Using where |
      +------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+
      

      now disabling derived_merge goes back to regular plan
      Is this expected that there is no computation of the materialization result in such case ?

      set optimizer_switch='derived_merge=off'; 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      MariaDB [sabre]> explain select count(*) from(   SELECT   s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8  )) as L;
      +------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
      | id   | select_type | table            | type   | possible_keys       | key     | key_len | ref                                  | rows | Extra                                                                            |
      +------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
      |    1 | PRIMARY     | <derived2>       | ALL    | NULL                | NULL    | NULL    | NULL                                 |  191 |                                                                                  |
      |    2 | DERIVED     | bmsql_district   | const  | PRIMARY             | PRIMARY | 8       | const,const                          |    1 |                                                                                  |
      |    2 | DERIVED     | bmsql_order_line | range  | PRIMARY             | PRIMARY | 12      | NULL                                 |  191 | Using where; Start temporary                                                     |
      |    2 | DERIVED     | bmsql_stock      | eq_ref | PRIMARY,s_item_fkey | PRIMARY | 8       | const,sabre.bmsql_order_line.ol_i_id |    1 | Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered scan |
      +------+-------------+------------------+--------+---------------------+---------+---------+--------------------------------------+------+----------------------------------------------------------------------------------+
      4 rows in set (0.01 sec)
      

      Attachments

        Activity

          People

            igor Igor Babaev
            stephane@skysql.com VAROQUI Stephane
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.