[SERVER-37059] Prefix of compound text indexes cannot be used for non-text search queries Created: 08/Sep/18  Updated: 27/Oct/23  Resolved: 10/Sep/18

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 3.6.7
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Adam Harrison Assignee: Kelsey Schubert
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to DOCS-10322 Clarify eligibility of compound index... Closed
Operating System: ALL
Participants:

 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. 



 Comments   
Comment by Adam Harrison [ 10/Sep/18 ]

Thanks Kelsey!

Comment by Kelsey Schubert [ 10/Sep/18 ]

Hi adamaharrison@gmail.com,

Thanks for reporting this behavior. My colleague, Stennie, wrote a detailed explanation of this behavior on stackoverflow and I recommend reviewing it.

I agree that the documentation could be improved to clarify that this behavior expected. Feel free to vote for DOCS-10322 and watch it for updates.

Kind regards,
Kelsey

Generated at Thu Feb 08 04:44:51 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.