Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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. {noformat} 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) {noformat} |
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. {noformat} 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) {noformat} 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. |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Attachment | dbt3-q22.txt [ 28700 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Axel Schwenke [ axel ] |
| Attachment | 04-opt1_explain.sql_1_results.txt [ 28704 ] | |
| Attachment | 04-opt2_explain.sql_1_results.txt [ 28705 ] |
| Attachment | q4-mrr-real-no-exists2in.png [ 29002 ] | |
| Attachment | q4-mrr-bka-real.png [ 29003 ] |
| Attachment | q4-mrr-real-no-exists2in-io-only.png [ 29004 ] |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 38100 ] | MariaDB v2 [ 43706 ] |
| Workflow | MariaDB v2 [ 43706 ] | MariaDB v3 [ 62865 ] |
| Workflow | MariaDB v3 [ 62865 ] | MariaDB v4 [ 132303 ] |