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

Window functions: condition pushdown through the PARTITION BY clause

    XMLWordPrintable

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: cvicentiu, igor, sanja.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              psergei Sergei Petrunia
              Votes:
              1 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.