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

cost-based optimizer choice for k-NN indexes

Details

    Description

      optimizer needs to be able to make a cost-based choice whether to use k-NN index for ORDER BY ... LIMIT or not

      Attachments

        Issue Links

          is blocked by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT

          • Major - Major loss of function.
          • Stalled

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33406 basic optimizer support for k-NN searches

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-32887 vector search

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33409 Index Condition Pushdown for k-ANN graph searches

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            adamluciano Adam Luciano added a comment -

            How much of a positive impact to performance may we see from enabling this feature?

            adamluciano Adam Luciano added a comment - How much of a positive impact to performance may we see from enabling this feature?
            serg Sergei Golubchik added a comment -

            the point is — optimizer needs to decide whether to use a vector index, a some other index, or a full table scan based on the cost of each execution plan, it needs to select the fastest.

            It doesn't do that now, so you can tweak the data to get any speedup you want. For example, something like

            SELECT * FROM t1 WHERE a=1 ORDER BY VEC_DISTANCE_EUCLIDEAN(vec, ...) LIMIT 5;

            If a=1 matches almost the whole table, then vector index will be the fastest execution plan. If a=1 matches only few rows out of millions, vector index will be a horrible choice.

            serg Sergei Golubchik added a comment - the point is — optimizer needs to decide whether to use a vector index, a some other index, or a full table scan based on the cost of each execution plan, it needs to select the fastest. It doesn't do that now, so you can tweak the data to get any speedup you want. For example, something like SELECT * FROM t1 WHERE a=1 ORDER BY VEC_DISTANCE_EUCLIDEAN(vec, ...) LIMIT 5; If a=1 matches almost the whole table, then vector index will be the fastest execution plan. If a=1 matches only few rows out of millions, vector index will be a horrible choice.

            People

              Unassigned Unassigned
              serg Sergei Golubchik
              Votes:
              0 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.