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

MariaDB does not normalize duplicate/commuted OR predicates, causing large execution-time difference for logically equivalent WHERE clauses

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 12.2.2
    • N/A
    • Optimizer
    • None
    • Not for Release Notes

    Description

      Two logically equivalent queries return the same result, but their execution times differ dramatically only because the OR predicates are ordered differently and one cheap predicate is duplicated.
      In the slower query, MariaDB evaluates an expensive expression REPEAT(s,n) before reaching the cheap condition z < 85. In the faster query, the cheap true condition z < 85 appears first, so the expensive expression can effectively be avoided by short-circuit evaluation.
      The optimizer appears not to normalize, reorder, or deduplicate the logically equivalent OR predicates. As a result, two equivalent WHERE clauses can have a very large and unexpected runtime difference.
      In my test, both queries returned COUNT = 1000, but the first query took about 0.0975 sec, while the second query took about 0.00023 sec, over 400x faster.

      How to repeat

      DROP DATABASE IF EXISTS perf_or_order;
      CREATE DATABASE perf_or_order;
      USE perf_or_order;
       
      CREATE TABLE t(
        s VARCHAR(10),
        n INT,
        z INT
      );
       
      CREATE TABLE d(n INT);
       
      INSERT INTO d VALUES
        (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      INSERT INTO t
      SELECT 'x', 50000, 0
      FROM d a JOIN d b JOIN d c;
       
      SET profiling = 1;
       
      SELECT SQL_NO_CACHE COUNT(*) FROM (
        SELECT REPEAT(s,n) AS q4, z
        FROM t
      ) AS sq
      WHERE
        ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL)
        OR z < 85;
       
      SELECT SQL_NO_CACHE COUNT(*) FROM (
        SELECT REPEAT(s,n) AS q4, z
        FROM t
      ) AS sq
      WHERE
        z < 85
        OR ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL)
        OR z < 85;
       
      SHOW PROFILES;
      

      Observed result

      Query 1 result: COUNT(*) = 1000
      Query 2 result: COUNT(*) = 1000
       
      Query 1 duration: about 0.0975 sec
      Query 2 duration: about 0.00023 sec
      

      Expected result
      Since the two predicates are logically equivalent, MariaDB should ideally normalize or simplify the duplicated/commuted OR condition so that both queries get comparable execution time. At minimum, the redundant duplicated predicate should not cause such a large performance difference.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              chen7897 cl hl
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.