Q13 from DBT3 uses table scan instead of covering index scan

[Axel Schwenke]

This is MariaDB-5.5.23 (GA, as released). DBT3 data at SF30 loaded into InnoDB tables. Default statistics (fluctuating). Running EXPLAIN for Q13 10 times, restarting the server in between. Yields 10 times the same (bad) plan. Earlier tests resulted in exactly this plan 100 times in 100 tries.

How to reproduce:

login to facebook-maria1

cd ~/benchmark/dbt3/mariadb-tools/dbt3_benchmark

~/benchmark/dbt3/mariadb-tools/dbt3_benchmark> time perl launcher.pl --project-home=/home/mariadb/benchmark/dbt3/ --results-output-dir=/home/mariadb/benchmark/dbt3/results/innodb_s30_test --datadir=/home/mariadb/benchmark/dbt3/db_data/ --test=/home/mariadb/benchmark/dbt3/mariadb-tools/dbt3_benchmark/tests/innodb_test_mariadb_5_5_Q13_for_igor.conf --queries-home=/home/mariadb/benchmark/dbt3/gen_query/ --scale-factor=30
(this is one single line)

after that there will be a new results directory "innodb_s30_test_<timestamp>" below ~/benchmark/dbt3/results.

Attached is the tarred results directory from the run I've done just now.

[Axel Schwenke (ahel)]

commandline saved in bug999251.sh. To reproduce:

cd ~/benchmark/dbt3/mariadb-tools/dbt3_benchmark
./bug999251.sh

this runs in about 1 minute

[Axel Schwenke (ahel)]

"InnoDB tables" means "XtraDB tables"

[Axel Schwenke (ahel)]

The very same execution plan was chosen for ANY run of Q13 at SF30. This includes default (volatile) statistics, persistent (but randomly gathered) statistics and exact statistics from MariaDB-MWL248.

I.e. here is the plan from MariaDB-5.5.23-MWL248

+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Using temporary; Using filesort |
| 2 | DERIVED | customer | ALL | NULL | NULL | NULL | NULL | 4500000 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 15 | 100.00 | Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+

Execution plans for all 3 kinds of statistics are archived in lp:~ahel/maria/mariadb-benchmarks in the dbt3-runs/innodb_s30_test_2012-04 subdirectory.

[Igor Babaev (igorb-seattle)]

I fixed the problem in MariaDB 5.5.
Probably it makes sense to fix it in MariaDB 5.3 as well (back-porting the patch) as this bug is a serious
performance issue.

I merged the fix into maria-5.5-mwl248.