Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Performance regression with sort_intersection in maria-5.1-wl21
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
Re: Performance regression with sort_intersection in maria-5.1-wl21
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
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)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
which is slower than the one with sort_intersection=off
Re: Performance regression with sort_intersection in maria-5.1-wl21
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)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Launchpad bug id: 686355
Re: Performance regression with sort_intersection in maria-5.1-wl21
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