[SERVER-7105] Please provide a way to use $hint for Update/FindAndModify, and/or fix the Query Optimizer Created: 21/Sep/12 Updated: 07/Mar/14 Resolved: 15/Oct/12 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Alex Lyman | Assignee: | Aaron Staple |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||
| Operating System: | Windows | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
We're having an issue where a FindAndModify in our code, in some of our environments/servers, decides to use an index that fails horribly (i.e. is sparse, when we're querying for nulls), and would like a way to force it to use a correct index. We're open to using an Update instead, if it would mean a faster turn-around on this issue. Our current example: SECONDARY> db.version() SECONDARY> db.ourcollection.getIndexes() , , SECONDARY> db.ourcollection.find( { "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : null }).explain() } SECONDARY> db.ourcollection.find( { "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ==") }, { _id: 1, StartedOn: 1 }).toArray() ] SECONDARY> db.ourcollection.find( { "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : null }).toArray() |
| Comments |
| Comment by Aaron Staple [ 10/Oct/12 ] | ||
|
Hi Alex - Are the workarounds working for you? I think the question of whether a sparse index should return different results was discussed in | ||
| Comment by Aaron Staple [ 24/Sep/12 ] | ||
|
Hi Alex, As you mentioned, there isn't a way to hint a find and modify. In 2.2 we added a policy where $exists:false queries will not use sparse indexes (
(Note I'm assuming you're just looking for docs without a StartedOn field.) On both 2.0 and 2.2 you can use the following workaround to prevent the sparse StartedOn index from being used:
| ||
| Comment by Alex Lyman [ 24/Sep/12 ] | ||
|
Aaron, Our specific case is to set StartedOn if it is not already set, and detect that our process is the one that set it. In particular, the process that does this runs on multiple servers, in multiple data centers, and after it does set it, broadcasts a message to other processes (not the ones that would be setting this). Our interface contract specifies that that message gets sent exactly once, thus why we use the {StartedOn: null}predicate. The use of find-and-modify isn't strictly necessary, but it does save a round-trip getting the document after it has been updated. This is a boiled down example of the interaction the process uses: PRIMARY> result = db.ourcollection.findAndModify({ , if (result != null) { broadcastMessage(result); } | ||
| Comment by Aaron Staple [ 24/Sep/12 ] | ||
|
Hi Alex, Can you send the find and modify query you are running? Why do you have a query { "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : null }, would _id be sufficient? One general thing you might do is run a find with a hint, get the _id of the document you want, and then update that document. If you send some more details on what you are doing I might be able to suggest some other solutions. |