Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28246

Optimizer uses all partitions during an update in MariaDB 10.6.x but not in 10.2.x

    XMLWordPrintable

Details

    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

        1. C25636c.png
          33 kB
          Sven Heidrich
        2. C25636d.png
          35 kB
          Sven Heidrich

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              SvenH Sven Heidrich
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.