Performance regression with sort_intersection in maria-5.1-wl21

[Philip Stoev]

The following query against the DBT-3 data set is much slower in maria-5.1-wl21 because it gets executed with sort_intersection

SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244);

The left side of the OR expression returns zero rows. Each side is easily computable with an index when executed separately.

[Philip Stoev (philip-stoev)]

In MariaDB-5.1 with scale factor 0.01

           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
          key: i_l_suppkey_partkey
      key_len: 10
          ref: NULL
         rows: 4009
        Extra: Using where; Using index

statistics: Handler_read_next:4003, Handler_read_key: 2
running time as reported by client: 0.00 sec

In maria-5.1-wl21

           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
          key: i_l_partkey,i_l_suppkey_partkey
      key_len: 5,10
          ref: NULL
         rows: 241
        Extra: Using sort_intersect(i_l_partkey,i_l_suppkey_partkey); Using where

execution time: 0.09

| Handler_read_key | 4 |
| Handler_read_next | 8040 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4003 |

[Philip Stoev (philip-stoev)]

DBT3 with scales 0.1 and 0.01 in mysqldump format is available at

http://bazaar.launchpad.net/~randgen/randgen/rqg2/files/head:/conf/dbt3/

click the arrow button at the right to download the file

[Philip Stoev (philip-stoev)]

To reproduce on a 32-bit machine:

bzr branch lp:~maria-captains/maria/maria-5.1-wl21 bug686355

cd bug686355

./BUILD/compile-pentium-debug-max-no-ndb

 wget http://bazaar.launchpad.net/~randgen/randgen/rqg2/download/philips%40eve-20101206145105-t8wwkvi1kbxdxwkn/dbt3s0.01.dump.bz2-20101206144900-b0ku0fait4lahr50-1/dbt3-s0.01.dump.bz2

bzip2 -d dbt3-s0.01.dump.bz2

 MTR_VERSION=1 perl mysql-test-run.pl --mysqld=--init-file=/home/philips/bzr/bug686355/dbt3-s0.01.dump --start-and-exit 1st

Then run

SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244);

The plan you should get is

mysql> explain SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
          key: i_l_partkey,i_l_suppkey_partkey
      key_len: 5,10
          ref: NULL
         rows: 241
        Extra: Using sort_intersect(i_l_partkey,i_l_suppkey_partkey); Using where
1 row in set (0.01 sec)

which is slower than the one with sort_intersection=off

[Igor Babaev (igorb-seattle)]

With the latest version of the tree maria-5.1-wl21 I had:

MariaDB [test]> select count(*) from lineitem;
+----------+
| count(*) |
+----------+
| 60175 |
+----------+
1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch='index_merge_sort_intersection=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
         type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
          key: i_l_suppkey_partkey
      key_len: 10
          ref: NULL
         rows: 4009
        Extra: Using where; Using index
1 row in set (0.00 sec)