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

Window Functions: Push condition into inner query

    XMLWordPrintable

Details

    Description

      At the moment optimizer does not push condition into Window function in SELECT part of inner query:

      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_100000;
      insert into t2 select seq, seq, seq from seq_1_to_100000;
       
      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 where (a, b, c) = (50000,50000,50000);
      

       
      +------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
      | id   | select_type | table      | type  | possible_keys | key  | key_len | ref                                 | rows   | Extra                                     |
      +------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
      |    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                                | 100000 | Using where                               |
      |    2 | DERIVED     | t1         | index | a             | a    | 15      | NULL                                | 100000 | Using where; Using index; Using temporary |
      |    2 | DERIVED     | t2         | ref   | a             | a    | 10      | tdsdbsgowner.t1.a,tdsdbsgowner.t1.b |      1 | Using index                               |
      +------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
      

      So optimizer is processing all rows for inner query instead of using WHERE condition from outer query

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.