Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-23808

Implement Index Choice based on Partial Filter Expression

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: 3.2.5
    • Fix Version/s: Backlog
    • Component/s: Indexing
    • Labels:
      None

      Description

      Request for index selection based solely on the partialFilterExpression.

      Currently, the query optimizer decides on an index based on the match and sort arguments of a query. With partial indexes, it will also consider the partialFilterExpression, but as implemented, it can still miss an index that would be performant on the query.

      Here's an example where the current implementation is sub-optimal:

      db.foo.dropIndex();
      db.foo.createIndex( { a : 1 }, { partialFilterExpression : { b : { $gte : 5 } } } );
      for (i=1; i<=10; i++) { db.foo.insertOne( { a : i, b : i } ) };
      db.foo.find( { b : { $gte : 5 } }, { _id : 0, a : 1 } ).explain()
      

      Desired result: The query uses the index, and it is a covered query
      Current result: the query performs a collection scan
      Variation: db.foo.find( { a : { $exists: true }, b : { $gte : 5 } }, { _id : 0, a : 1 } ).explain() results in an index scan, but does not cover the query even though the index could cover this query.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              backlog-server-query Backlog - Query Team
              Reporter:
              william.cross William Cross
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              13 Start watching this issue

                Dates

                Created:
                Updated: