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

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

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22248 Optimizer chooses wrong strategy on delete

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22537 optimizer_use_cond_selectivity > 1 can cause slow plans

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25480 Optimizer uses wrong index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Oleg Smirnov made transition -
            Open In Progress
            		5d 1h 59m 1
            Oleg Smirnov made transition -
            In Progress In Review
            		32d 17h 57m 1
            Sergei Petrunia made transition -
            In Review Stalled
            		3d 9h 16m 1
            Oleg Smirnov made transition -
            Stalled Closed
            		1d 18h 52m 1

            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.