Let's see if the optimizer is theoretically able to attach the subquery to the
right table for the FirstMatch plan:
On Thu, Aug 09, 2012 at 12:39:00PM +0400, Sergei Petrunia wrote:
> == Modified query ==
...
>
> == Benchmarking ==
>
> Number of times scalar-subquery was executed:
> Materialization: 9552 times (same as before)
> First-Match: 299 times (A LOT LESS!)
>
> Execution times:
> Materialization: 5.30 sec (same as before)
> First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats
> materialization)
>
Let's check whether the optimizer has sufficient input data to conclude that
moving the scalar-subquery from table partsupp to table part is advantageous.
The data about extra selectivity is basically tab->quick_condition_rows.
Let's take the EXPLAIN and its analysis from the "Cost analysis: optimizer
statistics vs real dataset properties" email. Relevant lines will start with
'NOTE>'.
+--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+
|id|select_type |table |type ||key |key_len|ref
|rows|filtered|Extra
|
+--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+
| 1|PRIMARY |nation |ref ||n_name |26 |const
| 1| 100.00|Using where; Using index; Using temporary;
Using filesort|
| 1|PRIMARY |supplier|ref ||i_s_nationkey |5
|nation.n_nationkey | 251| 100.00|
|
| 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4
|supplier.s_suppkey | 34| 100.00|Using where
|
| 1|PRIMARY |part |eq_ref||PRIMARY |4
|partsupp.ps_partkey | 1| 100.00|Using where;
FirstMatch(supplier) |
| 4|DEP. SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10
|partsupp.ps_partkey,partsupp.ps_suppkey| 3| 100.00|Using where
|
+--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+
5 rows in set, 3 warnings (0.01 sec)
=== nation: ===
like above, 1 row. OK.
NOTE> - "using where" won't filter anything out.
=== supplier: ===
see above, 251 rows, ~ok 412 real...
NOTE> - "using where" won't filter anything out.
=== partsupp: ===
- 800K rows and 10K distinct ps_suppkey, which gives rec_per_key=80 (EXPLAIN
shows 34)
- as for data that we will hit:
select count(*) from nation, supplier, partsupp
where s_suppkey=ps_suppkey and s_nationkey=n_nationkey and n_name='canada';
gives 32690 rows. 32690 / 412 = 80, matches rec_per_key.
NOTE> "using where" won't filter anything out (NOT TAKING the scalar-subquery
into account)
=== part: ===
- eq_ref, so exactly 1 match. We know DBT-3 dataset is such that it always has
one.
NOTE> "using where" is "p_name like forest"! it will filter stuff out!
I haven't checked, but I suppose that condition "p_name like forest%'(*) is not
correlated with the table access condition, 'p_partkey=partsupp_ps_partkey'.
The email 'Cost analysis: Materialization plan' shows a range access on (*),
with the estimate rows=2378.
Total number of rows in table part is is 200K, InnoDB's estimate is 200999.
This gives selectivity of 0.0118.
=== How many times scalar-subquery will be evaluated ===
Let's take estimate numbers:
1 * 251 * 34 * 1 * 0.0118 = 100.7012 evaluations.
If the estimates were perfect:
1 * 400 * 80 * 1 * 0.118 = 377.6 evaluations
As mentioned at the top of this email, in reality subquery is evaluated 299
times.
=== Conclusion#1 ===
quick_condition estimates provide information that will allow the optimizer to
make a correct conclusion about # of times that scalar-subquery will be
evaluated after table part.
What remains to be checked: check that our cost formulas will make the plan of
{ FirstMatch, scalar-subquery attached to table part}
cheaper than
{ FirstMatch, scalar-subquery attached to table partsupp}
and cheaper than the SJ-Materialization plan.
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
