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

Poor performance in query with filtered rank

    XMLWordPrintable

Details

    Description

      Can not achieve good performance in query with filtered columns outside of inner part with RANK() function. These columns participate in "partition by" expression of RANK(), bet filter isn't pushed down. Same functionality in Oracle works fine.

      Short example.

      Good performance:

      select ml_id, nr, da
      ,rank() over (partition by ml_id, nr, da order by nr) r
      from ml, iger, days
      where ml_id = 4 and nr = 1 and da = date(now())

      Bad performance (desired query):

      select * from (
      select ml_id, nr, da
      ,rank() over (partition by ml_id, nr, da order by nr) r
      from ml, iger, days
      ) a where ml_id = 4 and nr = 1 and da = date(now())

      Plan:

      1 PRIMARY <derived2> ALL \N \N \N \N 22991097024000 Using where
      2 DERIVED ml index \N ml_aggf_i 1 \N 12150 Using index; Using temporary
      2 DERIVED days index \N PRIMARY 3 \N 21760 Using index; Using join buffer (flat, BNL join)
      2 DERIVED iger index \N PRIMARY 3 \N 86961 Using index; Using join buffer (incremental, BNL join)

      What could be done to improve?

      Attachments

        Activity

          People

            Unassigned Unassigned
            balta Tadas Balaišis
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.