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

Prefix of compound text indexes cannot be used for non-text search queries

    • Type: Icon: Bug Bug
    • Resolution: Works as Designed
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.6.7
    • Component/s: None
    • Labels:
      None
    • ALL

      A non-text prefix of a compound text index is not able to be used for normal queries. It is unclear if this is expected behavior, i.e. text indexes can only be used for text search queries. 

      For example, consider the following: 

       

      db.foo.drop()db.foo.insert({ "x" : 1, "message" : "Hello world." })
      db.foo.insert({ "x" : 2, "message" : "" })
      db.foo.insert({ "x" : 3, "message" : "Hello there"})

      db.foo.createIndex({ "x" : 1, "message" : "text" }, { "background" : true } )

       

      For normal indexes, the prefix of this index ( { x: 1 } ) can be used for queries filtering on 'x'. However, the following query is unable to use prefix of the index:

      db.foo.find({{

      { "x" : 1 }

      }}).explain(true){{{}}
      {{ "queryPlanner" : {}}
      {{ "plannerVersion" : 1,}}
      {{ "namespace" : "test.foo",}}
      {{ "indexFilterSet" : false,}}
      {{ "parsedQuery" : {}}
      {{ "x" :}}{{

      { "$eq" : 1 }

      }}},
      {{ "winningPlan" : {}}
      {{ "stage" : "COLLSCAN",}}
      {{ "filter" : {}}
      {{ "x" :}}{{

      { "$eq" : 1 }

      }}},
      {{ "direction" : "forward"}}
      {{ },}}
      {{ "rejectedPlans" : [ ]}}
      {{ },}}
      {{ "executionStats" : {}}
      {{ "executionSuccess" : true,}}
      {{ "nReturned" : 1,}}
      {{ "executionTimeMillis" : 4,}}
      {{ "totalKeysExamined" : 0,}}
      {{ "totalDocsExamined" : 3,}}
      {{ "executionStages" : {}}
      {{ "stage" : "COLLSCAN",}}
      {{ "filter" : {}}
      {{ "x" :}}{{

      { "$eq" : 1 }

      }}},
      {{ "nReturned" : 1,}}
      {{ "executionTimeMillisEstimate" : 0,}}
      {{ "works" : 5,}}
      {{ "advanced" : 1,}}
      {{ "needTime" : 3,}}
      {{ "needYield" : 0,}}
      {{ "saveState" : 0,}}
      {{ "restoreState" : 0,}}
      {{ "isEOF" : 1,}}
      {{ "invalidates" : 0,}}
      {{ "direction" : "forward",}}
      {{ "docsExamined" : 3}}
      {{ },}}
      {{ "allPlansExecution" : [ ]}}
      {{ },}}
      {{ "serverInfo" :}}{{

      { // redacted }

      }}

      Hinting the index shows the query planner unable to bound on the index prefix. 

      The Restrictions section of text indexes documentation (https://docs.mongodb.com/manual/core/index-text/#restrictions) does not state that text indexes can only be used for text search. 

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            adamaharrison@gmail.com Adam Harrison
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: