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

Window functions: condition pushdown through the PARTITION BY clause

    Details

      Description

      MDEV-9197 adds support for pushdown of conditions into mergeable VIEWs (or derived tables).

      Suppose the child query

      • uses a window function
      • the window function has a PARTITION BY col1 clause

      Then, the condition on col1 can be pushed down through the window function.

      Example:

      select * from (
        select
          name,
          rank() over (partition by dept
                       order by incidents desc) as R
        from
          staff
      ) as TBL
      where dept='Support'
      

      Here, current execution will compute incidents across all departments. However, we are only interested in dept='Support'. PARTITION BY dept allows pushing the dept='Support' condition to the inner query's WHERE condition.

      Initial mention of this possible feature:
      https://www.percona.com/live/data-performance-conference-2016/sessions/window-functions-mariadb , slide #66.

      CC: Vicentiu Ciorbaru, Igor Babaev, Oleksandr Byelkin.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: