maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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