[SERVER-16350] Wrong index choice when request contains _id in query + sort command Created: 29/Nov/14  Updated: 25/Jun/15  Resolved: 03/Dec/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.6.5
Fix Version/s: None

Type: Bug Priority: Critical - P2
Reporter: Romain Pechayre Assignee: David Storch
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-15802 Query optimizer should always use equ... Closed
Related
related to SERVER-13211 Optimal index not chosen for query pl... Closed
related to SERVER-15225 CachedPlanStage should execute for tr... Closed
Operating System: ALL
Participants:

 Description   

Hello,

I encountered a strange behavior in Mongo 2.6.x. i am using Mongoid in ruby talk to MongoDB and Mongoid allows having default sort command. When I run the following command :

Person.find("someindex") in Mongoid, it is translated into
db.persons.find(

{deleted_at: null, _id: ObjectId("someindex")}

).sort(created_at: -1)

Mongoid is not really smart by generating such query because we know we will get either 0 or 1 result, so the sort operator does not make sense.

However, I've found cases where MongoDB is even less smart and caches the wrong index, including the created_at field.

I would expect MongoDB to automatically use the _id index whenever it is present in the query, no matter other fields that are present in the query, in partcular sort commands.

I already had a discussion about this on google group:
https://groups.google.com/forum/#!searchin/mongodb-user/rpechayr/mongodb-user/-btb_43ONfk/w0dMlECPInIJ



 Comments   
Comment by David Storch [ 03/Dec/14 ]

Hi rpechayr,

Thanks for the bug report. This appears to be a duplicate of SERVER-15802. I am going to resolve this ticket as a dup, but please watch SERVER-15802 for progress updates. As described in this comment MongoDB's query system is likely caching the query plan using non-_id index when that other index can more quickly return an answer of "zero matching documents" than {_id: 1}.

Our server development team has several open improvement tickets that could fix this and similar problems with caching bad plans:

  • SERVER-15225: This change would cause the query planner to evict a plan cache entry and re-plan the query as soon as it detects that a plan taken from the cache is performing poorly.
  • An "index pruning" strategy along the lines of SERVER-13211. That is, we could have logic in the index selection stage which detects that no index other than {_id: 1} is necessary to answer this query. In general, when we have an equality predicate over a unique index, the query system should be able to throw out any plan which will not execute as a single key-value lookup in a unique index.

I hope this response adequately addresses the concerns brought forth in this ticket. Please feel free to reach out with any further questions or comments.

Best,
Dave

Generated at Thu Feb 08 03:40:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.