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

Poor performance in query with filtered rank

    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

            • Assignee:
              Unassigned
              Reporter:
              balta Tadas BalaiĊĦis
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: