Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
-
Windows Server
Description
We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.
|
Simple Test |
USE test;
|
DROP TABLE IF EXISTS src;
|
DROP TABLE IF EXISTS trg;
|
|
|
CREATE TABLE src (
|
part INT(1), a INT(1),
|
b INT(1),
|
PRIMARY KEY (a,part),
|
INDEX b (b,part)
|
)ENGINE=InnoDB
|
PARTITION BY LIST (part) (
|
PARTITION Current VALUES IN (0),
|
PARTITION Relevant VALUES IN (1),
|
PARTITION Archive VALUES IN (2)
|
);
|
|
|
CREATE TABLE trg LIKE src;
|
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
|
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
|
|
|
EXPLAIN FORMAT=JSON
|
UPDATE trg JOIN src USING(a) SET trg.part=1
|
WHERE trg.part=1 AND src.part=2 ;
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
Looking at the history of this code I can see:
|
commit 3b6d90385230be911e15b9aa93a76e26367bc4bc |
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Tue Oct 23 11:23:34 2018 +0200
|
|
|
MDEV-17493: Partition pruning doesn't work for nested outer joins
|
|
|
Reuse the fix for MDEV-17518 here, too.
|
Determining prune_cond was extracted to separate function get_saragable_cond() and the logic has changed:
@@ -1770,19 +1773,9 @@ JOIN::optimize_inner()
|
List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables);
|
while ((tbl= li++))
|
{
|
- /*
|
- If tbl->embedding!=NULL that means that this table is in the inner
|
- part of the nested outer join, and we can't do partition pruning
|
- (TODO: check if this limitation can be lifted)
|
- */
|
- if (!tbl->embedding ||
|
- (tbl->embedding && tbl->embedding->sj_on_expr))
|
- {
|
- Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
|
- tbl->table->all_partitions_pruned_away= prune_partitions(thd,
|
- tbl->table,
|
- prune_cond);
|
- }
|
+ Item **prune_cond= get_sargable_cond(this, tbl->table);
|
+ tbl->table->all_partitions_pruned_away=
|
+ prune_partitions(thd, tbl->table, *prune_cond);
|
}
|
}
|
#endif
|
Part of get_saragable_cond() related to our case:
+static Item **get_sargable_cond(JOIN *join, TABLE *table)
|
<...>
|
+ else if (table->pos_in_table_list->embedding &&
|
+ !table->pos_in_table_list->embedding->sj_on_expr)
|
+ {
|
+ /*
|
+ This is the inner side of a multi-table outer join. Use the
|
+ appropriate ON expression.
|
+ */
|
+ retval= &(table->pos_in_table_list->embedding->on_expr);
|
+ }
|
+}
|
While debugging during execution of statement
EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ; |
I found out that table->pos_in_table_list->embedding is not NULL for trg table while table->pos_in_table_list->embedding->on_expr is NULL. This results in an empty sargable condition and thus no partition pruning.
While adding one more check to the condition:
+ else if (table->pos_in_table_list->embedding &&
|
+ table->pos_in_table_list->embedding->on_expr &&
|
+ !table->pos_in_table_list->embedding->sj_on_expr)
|
seems to fix the issue and doesn't break the tests, I'm not sure embedding is set correctly here for the trg table. This doesn't look to be the case mentioned in the comment
/*
|
+ This is the inner side of a multi-table outer join. Use the
|
+ appropriate ON expression.
|
+ */
|
If embedding is set correctly then we probably need to update the comment to describe other possible scenarios. If not - I'll go looking for the code responsible for that.
psergei, any ideas on that?
oleg.smirnov, please check why prune_partitions prunes different sets of partitions in UPDATE and SELECT, and then let's discuss it.