[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:
Related
related to SERVER-2677 Allow count to work with query hints Closed
is related to SERVER-1599 support ability to pass hint to update Closed
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()
2.0.3

SECONDARY> db.ourcollection.getIndexes()
[
{
"v" : 1,
"key" :

{ "_id" : 1 }

,
"ns" : "ourdb.ourcollection",
"name" : "id"
},
{
"v" : 1,
"key" :

{ "StartedOn" : -1 }

,
"ns" : "ourdb.ourcollection",
"name" : "StartedOn_-1",
"background" : true,
"sparse" : true
}
]

SECONDARY> db.ourcollection.find(

{ "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : null }

).explain()
{
"cursor" : "BtreeCursor StartedOn_-1",
"nscanned" : 30,
"nscannedObjects" : 30,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :

{ "StartedOn" : [ [ null, null ] ] }

}

SECONDARY> db.ourcollection.find(

{ "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ==") }

,

{ _id: 1, StartedOn: 1 }

).toArray()
[

{ _id: BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ==") }

]

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 SERVER-3918. If you don't have any other concerns I'll plan to close this ticket.

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 (SERVER-3918). So with 2.2 you can change the query part of your find and modify to get the results you want:

{ "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : { $exists : false } }

(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:

{ "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), { $and:[ { $or:[ { "StartedOn" : null }, { "StartedOn": null } ] } ] } }

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({
query:

{ "_id" : new BinData(3,"L9UXPwbYdEiSQ68DG7A1iQ=="), "StartedOn" : null }

,
update: {$set: {StartedOn: ISODate()}},
new: true
});

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.

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