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

Support Index Condition Pushdown for clustered PK scans

    XMLWordPrintable

Details

    Description

      The following test will lock 3 records and their preceding gaps inside InnoDB, even though only the 2 first records match the condition id<=20:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t1(id INT PRIMARY KEY, c INT NOT NULL) ENGINE=InnoDB;
      		 
      INSERT INTO t1 VALUES(10,10),(20,20),(30,30),(40,40);
      		 
      SELECT * FROM t1 WHERE id<=20 FOR UPDATE;
      		 
      DROP TABLE t1;

      InnoDB cannot be blamed for this, because the SQL layer is not invoking ha_innobase::idx_cond_push(), so prebuilt->idx_cond will be NULL and InnoDB cannot know that the id=30 record does not match the WHERE condition.

      To confirm this:

      break ha_innobase::create
      		 
      continue
      		 
      finish
      		 
      break lock_rec_lock
      		 
      break ha_innobase::delete_table
      		 
      continue

      There will be 3 calls to lock_rec_lock() before the DROP TABLE. There should be only 2 calls, with type=LOCK_ORDINARY to lock the keys id=10,id=20 and the gaps preceding them.

      Another reason to enable index condition pushdown (ICP) for table scan (or PRIMARY KEY scan) is that if the query would return BLOBs, it would be much more efficient to avoid copying and throwing away the BLOBs of non-matching rows.

      Attachments

        Issue Links

          is blocked by

          Task - A task that needs to be done. MDEV-16232 Use fewer mini-transactions

          • Major - Major loss of function.
          • Stalled
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27999 select~for update skip locked locks unnecessary record

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21794 Optimizer flag rowid_filter leads to long query

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33533 Crash at execution of DELETE when trying to use rowid filter

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

          PERF-196 Loading...

          Activity

            People

              psergei Sergei Petrunia
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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