Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3
Description
Filing this based on observations made for MDEV-39005.
Consider this
create table t1 ( |
a int |
);
|
insert into t1 select seq from seq_1_to_5; |
create table t2 ( |
grp_id int, |
a int, |
b varchar(100), |
index (grp_id) |
);
|
insert into t2 select |
A.seq,
|
B.seq,
|
concat(A.seq, '-', B.seq) |
from
|
seq_1_to_200 A,
|
seq_1_to_10000 B;
|
with GROUP BY grp_id this will give 200 groups, 10K rows in each.
Just another table into the subquery. Not really important:
create table t3 ( |
b int, |
index(b) |
);
|
insert into t3 select seq from seq_1_to_1000; |
analyze table t1,t2,t3; |
The query:
Q1, with join:
explain select * |
from
|
t1,
|
(select |
grp_id,
|
sum(t2.a), |
count(t2.b) |
from |
t2 left join t3 on t3.b=t2.b |
group by grp_id |
) DT
|
where
|
DT.grp_id = t1.a ;
|
Q2, without JOIN:
explain select * |
from
|
t1,
|
(select |
grp_id,
|
sum(t2.a), |
count(t2.b) |
from |
t2
|
group by grp_id |
) DT
|
where
|
DT.grp_id = t1.a ;
|
Let's start with Q2.
Both before and after fix for MDEV-39005, we get non-split-materialized plan:
+------+-------------+------------+------+---------------+------+---------+---------+---------+----------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+------+---------------+------+---------+---------+---------+----------+---------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j2.t1.a | 1 | 100.00 | |
|
| 2 | DERIVED | t2 | ALL | grp_id | NULL | NULL | NULL | 1994924 | 100.00 | Using temporary; Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+---------+---------+----------+---------------------------------+
|
We read about 2M records in table t2 to produce 200 GROUP BY groups. Of those, we need 5.
If I add /*+ SPLIT_MATERIALIZED(DT) */ hint, I get
+------+-----------------+------------+------+---------------+--------+---------+---------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-----------------+------------+------+---------------+--------+---------+---------+------+----------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j2.t1.a | 1 | 100.00 | |
|
| 2 | LATERAL DERIVED | t2 | ref | grp_id | grp_id | 5 | j2.t1.a | 9924 | 100.00 | |
|
+------+-----------------+------------+------+---------------+--------+---------+---------+------+----------+-------------+
|
Here, we ill read 5 * 10K =50K rows from t2. That's 40x fewer than in the plan that was picked by default.
Costs
Look at the attached files.
without splitting, the cost is 3602.
With splitting:
The cost of derived table access is 235,179.5843
The cost of of filling the split-materialized table is: 20.99251572
Analysis
The problem seems to be here:
double oper_cost= (split_card * |
spl_postjoin_oper_cost(thd, split_card, rec_len));
|
We have split_card=9924.
Note that we both pass split_card down into spl_postjoin_oper_cost and also multiply its return value.
inside spl_postjoin_oper_cost, the costs are multiplied by its second parameter:
static
|
double spl_postjoin_oper_cost(THD *thd, double join_record_count, uint rec_len) |
{
|
double cost; |
TMPTABLE_COSTS tmp_cost= get_tmp_table_costs(thd, join_record_count,
|
rec_len, 0, 1);
|
/* cost to fill tmp table */ |
cost= tmp_cost.write * join_record_count;
|
/* cost to perform post join operation used here */ |
cost+= tmp_cost.lookup * join_record_count;
|
...
|
return cost; |
spl_postjoin_oper_cost() returns 4.738. The number is comparable with the cost to run the child join, 20.992 (This is when doing splitting, reading one GROUP BY group).
But after we multiply 4.7 * 9949 = 47021 - the number stops making any sense.
Second question about the costs
(Maybe this needs a separate MDEV? )
And then, after oper_cost, we have this (I've just added the //TODO comment) :
// TODO: why do we just take the costs from the last table? |
spl_plan->cost= (join->best_positions[join->table_count-1].read_time +
|
oper_cost);
|
So, this takes the cost from the last table in the child join...
Then the comparison to between split and unsplit costs is:
chosen= ((refills * spl_plan->cost + COST_EPS <
|
spl_opt_info->unsplit_cost) || spl_opt_info->hint_forced_split);
|
unsplit_cost also comes from the last table:
|
spl_opt_info->unsplit_cost= (best_positions[table_count-1].read_time +
|
spl_opt_info->unsplit_oper_cost);
|
but WHY do we compare unsplit costs?
Could it be that the last table in the join order is NOT the table that splitting is done on?
One can use OUTER JOIN and have the last table in the join order be totally irrelevant and cheap lookup table...