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

Optimizer does not eliminate duplicate identical IN subquery predicates, causing repeated full scan

    XMLWordPrintable

Details

    • Related to performance

    Description

      Two semantically equivalent queries have different execution cost. The second query only duplicates the same IN (subquery) predicate with AND, so the result is unchanged. However, MariaDB executes the duplicated subquery separately, causing an additional full scan of inner_t.
      Observed on MariaDB 12.2.2-MariaDB-ubu2404:

      original: ~0.0107s
      		 
      mutated:  ~0.0226s

      EXPLAIN also shows the original query scans inner_t once, while the duplicated-predicate query scans inner_t twice.

      How to repeat

      DROP DATABASE IF EXISTS perf_dup;
      		 
      CREATE DATABASE perf_dup;
      		 
      USE perf_dup;
      		 
       
      		 
      CREATE TABLE outer_t (
      		 
        id INT PRIMARY KEY
      		 
      );
      		 
       
      		 
      CREATE TABLE inner_t (
      		 
        v INT NOT NULL,
      		 
        pad VARCHAR(50)
      		 
      );
      		 
       
      		 
      INSERT INTO outer_t VALUES (42);
      		 
       
      		 
      CREATE TABLE d (n INT);
      		 
      INSERT INTO d VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
       
      		 
      INSERT INTO inner_t(v, pad)
      		 
      SELECT (a.n + 10*b.n + 100*c.n + 1000*d1.n + 10000*e.n) % 100,
      		 
             REPEAT('x', 50)
      		 
      FROM d AS a
      		 
      JOIN d AS b
      		 
      JOIN d AS c
      		 
      JOIN d AS d1
      		 
      JOIN d AS e;
      		 
       
      		 
      EXPLAIN
      		 
      SELECT COUNT(*)
      		 
      FROM outer_t
      		 
      WHERE id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      );
      		 
       
      		 
      EXPLAIN
      		 
      SELECT COUNT(*)
      		 
      FROM outer_t
      		 
      WHERE id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      )
      		 
      AND id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      );
      		 
       
      		 
      SET profiling = 1;
      		 
       
      		 
      SELECT COUNT(*)
      		 
      FROM outer_t
      		 
      WHERE id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      );
      		 
       
      		 
      SELECT COUNT(*)
      		 
      FROM outer_t
      		 
      WHERE id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      )
      		 
      AND id IN (
      		 
        SELECT v FROM inner_t WHERE pad LIKE 'x%'
      		 
      );
      		 
       
      		 
      SHOW PROFILES;

      Expected:
      The optimizer should recognize the duplicated identical predicate and avoid evaluating the same IN subquery twice.
      Observed:
      The duplicated-predicate query scans inner_t twice and is about 2x slower.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40434 Duplicate identical EXISTS predicates are materialized twice, causing ~2x slowdown

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40437 MariaDB does not normalize duplicate/commuted OR predicates, causing large execution-time difference for logically equivalent WHERE clauses

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40438 MariaDB does not eliminate duplicated identical deterministic scalar predicates

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40439 MariaDB evaluates expensive ALL subquery before a cheap false AND predicate, causing large performance difference

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40472 Redundant IS NOT NULL on SHA2() expression causes extra evaluation and slower execution

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40474 Redundant outer DISTINCT creates an additional temporary table above EXCEPT in MariaDB

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              psergei Sergei Petrunia
              chen7897 cl hl
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.