[MDEV-28246] Optimizer uses all partitions during an update in MariaDB 10.6.x but not in 10.2.x Created: 2022-04-06  Updated: 2022-05-24  Resolved: 2022-05-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Partitioning
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4

Type: Bug Priority: Major
Reporter: Sven Heidrich Assignee: Oleg Smirnov
Resolution: Fixed Votes: 0
Labels: optimizer, partitioning, update
Environment:

Windows Server

Attachments: PNG File C25636c.png     PNG File C25636d.png    
Issue Links:
Relates
relates to MDEV-22248 Optimizer chooses wrong strategy on d... Open
relates to MDEV-22537 optimizer_use_cond_selectivity > 1 ca... Closed
relates to MDEV-25480 Optimizer uses wrong index Stalled

 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 ;



 Comments   
Comment by Sergei Petrunia [ 2022-04-06 ]

oleg.smirnov, please check why prune_partitions prunes different sets of partitions in UPDATE and SELECT, and then let's discuss it.

Comment by Oleg Smirnov [ 2022-04-13 ]

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?

Comment by Sergei Petrunia [ 2022-05-17 ]

Ok to push after addressing trivial input provided on Slack.

Comment by Oleg Smirnov [ 2022-05-19 ]

Pushed into 10.2, to be merged upstream.

Generated at Thu Feb 08 09:59:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.