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

Window functions: reuse sorting and/or scanning

    XMLWordPrintable

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

            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.