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

Plan selection takes forever with MDEV-28852 comparing to reasonable time without

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.10.1
    • Component/s: Optimizer
    • Labels:
      None

      Description

      By "forever" in the summary I mean that I have never seen it finish, the longest I waited before interrupting was ~1 hour.

      The test uses all default server startup options, loads original data set from MDEV-28073 (xpo_full_testcase.sql), runs ANALYZE PERSISTENT FOR ALL on all tables, and executes EXPLAIN for a query structured identically to MDEV-28073 (minus a couple of last JOINs), but the tables and fields in the query are mixed up - that is, different tables and fields from the dataset appear in different JOIN positions and conditions. The exact query will be provided.

      A slight abnormality of this particular query is that one of ON conditions compares a datetime field to a string literal (not convertible into a proper date time), and thus produces Truncated incorrect datetime value warning (ER_TRUNCATED_WRONG_VALUE). I'm not sure whether it's a decisive factor for the problem, but it's possible. Except for this condition, the query is identical to MDEV-28928.

      The query was executed with EXPLAIN FORMAT=JSON on several different versions/builds for comparison. All builds are non-debug.

      preview-10.10-optimizer f332260c98

      |  4 | root | localhost:36260 | test | Query   |  590 | Statistics | EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
      ...
      

      It looks the same with optimizer_prune_level=2 (default) and 1.

      preview-10.10-optimizer, MDEV-28852 baseline (07a31de109)

      MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM (((((((((((((((( 
      ...
      1 row in set, 1 warning (36.832 sec)
      

      10.6 main 0e4cf497

      MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM (((((((((((((((( 
      ...
      1 row in set, 1 warning (9.314 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.