[SERVER-28957] Index bounds are not detected correctly Created: 25/Apr/17  Updated: 08/May/17  Resolved: 08/May/17

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

Type: Bug Priority: Major - P3
Reporter: Alexander Komyagin Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-13197 Tighten index bounds and allow compou... Backlog
Participants:

 Description   

Documents with two fields: {sort:..,x:...}
Index on {sort:1,x:1}

The query on "x" sorted by the sort field doesn't detect proper index bounds for x:

> db.test.find({x:{$gte:1,$lte:3}}).sort({sort:1}).explain("executionStats").executionStats.executionStages.inputStage.indexBounds
{ "sort" : [ "[MinKey, MaxKey]" ], "x" : [ "[MinKey, MaxKey]" ] }

However, adding an all-inclusive condition on "sort" into the query fixes the problem:

> db.test.find({sort:{$gte:MinKey},x:{$gte:1,$lte:3}}).sort({sort:1}).explain("executionStats").executionStats.executionStages.inputStage.indexBounds
{ "sort" : [ "[MinKey, MaxKey]" ], "x" : [ "[1.0, 3.0]" ] }



 Comments   
Comment by David Storch [ 08/May/17 ]

I am closing as a duplicate of SERVER-13197. The scenarios are slightly different (this one uses a sort, the other uses a hint) but the essence is the same: when there is no predicate on the leading field of the index, the query planner is not able to constrain the index bounds of the trailing fields.

There is no fundamental reason that we couldn't construct bounds for the trailing fields. However, the planner has quite a bit of index selection and plan enumeration logic which depends on differential treatment of leading versus trailing fields of a compound index. Extending the planner to generate better index bounds in cases like this one will therefore involve significant development effort from the Query Team.

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