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

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Works as Designed
    • Icon: Major - P3 Major - P3
    • None
    • 3.6.7
    • None
    • None
    • ALL

    Description

      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. 

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: