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

Add Optimizer Switch for Filesort with Small LIMIT Optimization

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      In our environment we have several types of queries that use the filesort with small LIMIT optimization poorly. It causes some of our major queries to basically full scan the table instead of using a non-orderable index that is more effective. We were previously able to work around this by using "SET STATEMENT optimizer_switch='extended_keys=on FOR" but that has stopped working for us since upgrading from 10.1 to 10.2. I've tried using engine independent statistics, too but that didn't seem to help. We are currently using IGNORE INDEX in some cases but that is not ideal since the index we need to ignore is sometimes useful for filtering. Would it be possible to add an optimizer switch or some other config to control it?

      Attachments

        Issue Links

          Activity

            This may offer a temporary work around for people affected by this optimization choosing a bad plan.

            bradjorgensen Brad Jorgensen added a comment - This may offer a temporary work around for people affected by this optimization choosing a bad plan.
            varun Varun Gupta (Inactive) added a comment - - edited

            Could you share an example demonstrating the problem. Internally in the optimizer we try to use and index (non-ordered) when we have LIMIT clause as this would help us to filter rows (range scan is used in such cases).

            varun Varun Gupta (Inactive) added a comment - - edited Could you share an example demonstrating the problem. Internally in the optimizer we try to use and index (non-ordered) when we have LIMIT clause as this would help us to filter rows (range scan is used in such cases).

            People

              psergei Sergei Petrunia
              bradjorgensen Brad Jorgensen
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.