maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04829
Cost analysis: FirstMatch plan
Hi Timour,
Please find below cost analysis for the plan with FirstMatch strategy.
Debugging with optimizer_switch='semijoin=off', query:
explain extended
select sql_calc_found_rows
s_name, s_address
from nation straight_join supplier
where s_suppkey in (select ps_suppkey from partsupp
where ps_partkey in (select p_partkey from part
where p_name like 'forest%')
and ps_availqty >
(select 0.5 * sum(l_quantity)
from lineitem
where l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date('1994-01-01')
and l_shipdate < date('1994-01-01') +
interval '1' year ))
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by s_name
limit 10;
+----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+
|id |select_type |table |type ||key |key_len|ef |rows|Extra |
+----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+
| 1|PRIMARY |nation |ALL ||NULL |NULL |NULL | 25|Using where; Using temporary; Using filesort|
| 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 180| |
| 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 39|Using where |
| 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1|Using where; FirstMatch(supplier) |
| 4|DEPENDENT SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 4|Using where |
+----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+
===== 1. nation ===
(gdb) p position->records_read
$367 = 25
(gdb) p position->read_time
$368 = 1
(gdb) p current_record_count / TIME_FOR_COMPARE
$369 = 5
(gdb) p current_read_time
$370 = 6
===== 2. supplier ===
(gdb) p position->records_read
$377 = 180
(gdb) p position->read_time
$378 = 4525
(gdb) p current_record_count / TIME_FOR_COMPARE
$379 = 900
(gdb) p current_read_time
$380 = 5431
## here it considers "part, partsupp" extension, with cost
## 12,984,927. I skip that, because that's not the optimal plan
===== 3. partsupp ===
(gdb) p position->records_read
$411 = 39
(gdb) p position->read_time
$412 = 180000
(gdb) p current_record_count / TIME_FOR_COMPARE
$413 = 35100
(gdb) p current_read_time
$414 = 220531
===== 4. part ===
(gdb) p position->records_read
$421 = 1
(gdb) p position->read_time
$422 = 175500
(gdb) p current_record_count / TIME_FOR_COMPARE
$423 = 35100
(gdb) p current_read_time
$424 = 431131
> advance_sj_state()
> Firstmatch_picker::check_qep
> optimize_wo_join_buffering
### It doesn't make any best_access_path() calls here, because no table is
### using join buffering.
### It forgets to add (current_record_count / TIME_FOR_COMPARE), and ends
### up with a slightly lower cost of
(gdb) print cost
$425 = 360931
> optimize_wo_join_buffering
> Firstmatch_picker::check_qep
## FirstMatch is chosen as the way we will use to eliminate duplicates..
## At the end of advance_sj_state, we have:
(gdb) p *current_read_time
$426 = 360931
(gdb) p *current_record_count
$427 = 4500
> advance_sj_state().
And this is what is chosen as the final query plan.
--
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
Follow ups