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

Window functions: reuse sorting and/or scanning

Details

    Description

      The standard says this:

      In general, two <window function>s are computed independently, each one
      performing its own sort of its data, even if they use the same data and the same <sort specification list>
      ...
      Nevertheless, the user may desire that two <window function>s be computed using the same ordering
      ...
      Two <window function>s are computed using the same (possibly non-deterministic) window ordering of the
      rows if any of the following are true:

      • (3 long clauses)

      This is a good reason to implement an optimization:

      if window functions W1 and W2 use compatible sortings, 
        they must share the filesort() call
      

      Let's call the above *filesort sharing*.

      The second step would be to share the "scan through the filesort result and compute the window function" step. Sharing the scan between window functions ought to give a speedup, because

      • we can compute values of all window functions and call h->update_row once (instead of many times)
      • It is more cache-friendly to do things in one pass, not in many.

      A question: should the scan be shared across all window functions or only those that have identical PARTITION BY clause?

      Attachments

        Issue Links

          Activity

            filesort sharing is supposed to work now, except that there is a known bug: MDEV-9877

            psergei Sergei Petrunia added a comment - filesort sharing is supposed to work now, except that there is a known bug: MDEV-9877

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.