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

Improvement: make derived_with_keys not generate identical draft keys

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      derived_with_keys optimization works by generating "draft" keys for the derived temporary table and feeding them into the optimizer.

      The "draft" keys can duplicate each other, as well as duplicate keys generated by other optimizations, e.g. IN-list-to-subquery optimization.

      This task is about not generating draft keys that are duplicate of each other. Generate just one key and use it multiple times.

      An example where one can see multiple identical keys are generated:

      CREATE TABLE t1 (l_orderkey int);
      		 
      INSERT INTO t1 VALUES (1),(2);
      		 
      CREATE TABLE t2 (o_orderkey int);
      		 
      INSERT INTO t2 VALUES (3),(4);
      		 
      SET IN_PREDICATE_CONVERSION_THRESHOLD= 2;
      		 
      SET OPTIMIZER_SWITCH= 'derived_with_keys=on';
      		 
      SELECT * FROM t1 JOIN t2 ON (l_orderkey = o_orderkey) WHERE l_orderkey IN (1, 2, 3);

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26591 Is MariaDB really caching or reusing “WITH” intermediary results?

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30017 MariaDB optimizer significantly slower with CTE than competitive product

          • Major - Major loss of function.
          • Open

          Activity

            HHN Hari Hara Naveen S added a comment -

            Is anyone working on this improvement right now? I interested in helping out.

            I have tried to read into the documentation to better understand the enhancement requested. From my understanding every time we create a derived temporary table we seem to be building an index on it, and we wish to build the index only once and try to use it multiple times.

            I can also start working on this improvement if someone could link a couple resources or guide me on how I can start implementing it.

            HHN Hari Hara Naveen S added a comment - Is anyone working on this improvement right now? I interested in helping out. I have tried to read into the documentation to better understand the enhancement requested. From my understanding every time we create a derived temporary table we seem to be building an index on it, and we wish to build the index only once and try to use it multiple times. I can also start working on this improvement if someone could link a couple resources or guide me on how I can start implementing it.

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              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.