[Commits] Rev 3391: BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3 in file:///home/psergey/dev2/5.3-look46/

Sergey Petrunya psergey at askmonty.org
Fri Jan 20 20:18:50 EET 2012


At file:///home/psergey/dev2/5.3-look46/

------------------------------------------------------------
revno: 3391
revision-id: psergey at askmonty.org-20120120181848-9a10zff055egjgd0
parent: psergey at askmonty.org-20120119221153-vrvfy11wccbr47ql
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look46
timestamp: Fri 2012-01-20 22:18:48 +0400
message:
  BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3
  - Added SJ_MATERIALIZATION_LOOKUP_MATCH_RATIO=0.75, made the optimizer assume 
    that this is the probability that a lookup in semi-join materialized table will
    produce a match.
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-01-10 21:26:00 +0000
+++ b/mysql-test/r/subselect.result	2012-01-20 18:18:48 +0000
@@ -1374,7 +1374,7 @@
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-01-10 21:26:00 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-01-20 18:18:48 +0000
@@ -1381,7 +1381,7 @@
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-01-10 21:26:00 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-01-20 18:18:48 +0000
@@ -1377,7 +1377,7 @@
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-01-10 21:26:00 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-01-20 18:18:48 +0000
@@ -1380,7 +1380,7 @@
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-01-10 21:26:00 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-01-20 18:18:48 +0000
@@ -1377,7 +1377,7 @@
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2012-01-19 19:44:43 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2012-01-20 18:18:48 +0000
@@ -762,9 +762,10 @@
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join buffer (incremental, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch((sj-nest)); Using join buffer (incremental, BNL join)
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
 drop table t2, t3;
 #
 # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3
@@ -893,7 +894,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
-1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	5	test.t3.a	1	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-19 19:44:43 +0000
+++ b/sql/opt_subselect.cc	2012-01-20 18:18:48 +0000
@@ -2389,7 +2389,7 @@
         after the QEP has been chosen.
       */
       *read_time=    mat_read_time;
-      *record_count= prefix_rec_count;
+      *record_count= prefix_rec_count * SJ_MATERIALIZATION_LOOKUP_MATCH_RATIO;
       *handled_fanout= new_join_tab->emb_sj_nest->sj_inner_tables;
       *strategy= SJ_OPT_MATERIALIZE;
       return TRUE;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-19 22:11:53 +0000
+++ b/sql/sql_select.cc	2012-01-20 18:18:48 +0000
@@ -5577,6 +5577,11 @@
     }
   }
 
+  if (best_key && s->table->pos_in_table_list->jtbm_subselect)
+  {
+    records *= SJ_MATERIALIZATION_LOOKUP_MATCH_RATIO;
+  }
+
   /* Update the cost information for the current partial plan */
   pos->records_read= records;
   pos->read_time=    best;
@@ -7185,6 +7190,15 @@
       to access join->best_positions[]. 
     */
     j->records_read= (ha_rows)join->best_positions[tablenr].records_read;
+
+    /* 
+      Avoid displaying 0 for fanouts < 1.
+      We should just make change JOIN_TAB::records_read from ha_rows to double,
+      but for now will only do this special-case rounding:
+    */
+    if (!j->records_read && join->best_positions[tablenr].records_read > 0.5)
+      j->records_read= 1;
+
     join->map2table[j->table->tablenr]= j;
 
     /* If we've reached the end of sjm nest, switch back to main sequence */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-10 21:26:00 +0000
+++ b/sql/sql_select.h	2012-01-20 18:18:48 +0000
@@ -696,6 +696,13 @@
 };
 
 
+/*
+  SJ-Materialization (both for merged and non-merged semi-joins will assume
+  a lookup in the materialization table will produce this many matches:
+*/
+const double SJ_MATERIALIZATION_LOOKUP_MATCH_RATIO =  0.75;
+
+
 class Sj_materialization_picker : public Semi_join_strategy_picker
 {
   bool is_used;



More information about the commits mailing list