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

Optimization for equi-joins of derived tables with window functions

    XMLWordPrintable

    Details

      Description

      At the moment optimizer does window function materialization even if join produces no rows:

      create or replace table t1 (a int, b int, c int, index(a, b, c));
      create or replace table t2 (a int, b int, c int, index(a, b, c));
       
      insert into t1 select seq, seq, seq from seq_1_to_500000;
      insert into t2 select seq, seq, seq from seq_1_to_500000;
       
      explain
      select * from 
      (
      select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )
      from t1 join t2
      where 
      t1.a=t2.a and t1.b=t2.b
      ) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);
      

      +------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
      | id   | select_type | table      | type   | possible_keys | key  | key_len | ref                 | rows   | Extra                                     |
      +------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
      |    1 | PRIMARY     | <derived3> | system | NULL          | NULL | NULL    | NULL                |      1 |                                           |
      |    1 | PRIMARY     | <derived2> | ref    | key0          | key0 | 15      | const,const,const   |     10 |                                           |
      |    3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL                |   NULL | No tables used                            |
      |    2 | DERIVED     | t1         | index  | a             | a    | 15      | NULL                | 500000 | Using where; Using index; Using temporary |
      |    2 | DERIVED     | t2         | ref    | a             | a    | 10      | test.t1.a,test.t1.b |      1 | Using index                               |
      +------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
      

      MariaDB [test]> select count(*) from
          -> (
          -> select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )
          -> from t1 join t2
          -> where
          -> t1.a=t2.a and t1.b=t2.b
          -> ) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (2.27 sec)
      

      For good performance it is required to skip materialization at all if no rows are joined and try to push condition if only few rows are joined like in MDEV-13385

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration