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

Optimization for equi-joins of derived tables with GROUP BY

Details

    • 10.2.12

    Description

      Consider the following tables

      create table t1 (a int);
      insert into t1 values (5), (1), (2), (9), (7), (2), (7);
      create table t2 (a int, b int, index idx(a));
      insert into t2 values (7,10), (1,20), (2,23), (7,18), (1,30), (4,71), (3,15), (7,82);
      

      and the query

      select t1.a,t.max,t.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t on t1.a=t.a;
      

      The following re-writing could be applied to this query:

      =>
      select t1.a,tl.max,tl.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 where  t1.a=t2.a) tl on 1=1;
      

      The result of this re-writing is a query with so-called lateral derived table. This query requires refilling of the temporary table created the derived table for every new value t1.a. As the size of the derived table tl usually is much smaller than the size of the derived table t this transformation will be always beneficial. Especially this transformation will be beneficial when the join operation that joins derived table is used in a multi-way join query where only a few records of t1 are selected.
      Unfortunately now we do not support lateral derived tables on SQL level. This task will allow to use them internally for this transformation.

      The difference of this task from the task MDEV-13225 that in this task the execution plan chosen for the original derived table will be just transformed into the one that uses a lateral derived table. As a result it might happen that the optimizer chooses not the best join order for the main query. The cost of using lateral derived table will be taken into account by the optimizer later.

      Attachments

        Issue Links

          Activity

            Transition Time In Source Status Execution Times
            Igor Babaev (Inactive) made transition -
            Open In Progress
            3d 8h 49m 1
            Igor Babaev (Inactive) made transition -
            In Progress In Review
            155d 3h 45m 1
            Igor Babaev (Inactive) made transition -
            In Review Stalled
            22d 18h 52m 1
            Igor Babaev (Inactive) made transition -
            Stalled Closed
            1m 42s 1

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.