Set hint for update to use indexes

Question

As per documentation it is possible to provide a hint to an update.
Now I'm using the java mongo client and mongo collection to do an update.

For this update I cannot find any way to provide a hint which index to use.
I see for the update I'm doing a COLSCAN in the logs, so wanting to provide the hint.

this.collection.updateOne(
    or(eq("_id", "someId"), eq("array1.id", "someId")),
    and(
        addToSet("array1", new Document()),
        addToSet("array2", new Document())
    )
);

Indexes are available for both _id and array1.id I found out in the logs the query for this update is using a COLSCAN to find the document.

Anyone who can point me in the right direction?

Using AWS DocumentDB, which is MongoDB v3.6

Amazon DocumentDB is a separate implementation from the MongoDB server. DocumentDB uses the MongoDB 3.6 wire protocol, but there are number of [functional differences](https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html) and the [supported commands](https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html) are a subset of those available in MongoDB 3.6. Support for hinting updates was added in MongoDB 4.2 so I suspect DocumentDB will not have this feature yet, but you'd have to review the DocumentDB documentation or test to confirm. — Stennie, Jan 01 '20 at 08:52

score 1 · Answer 1 · answered Jan 01 '20 at 02:23

Lets consider a document with an array of embedded documents:

{ _id: 1, arr: [ { fld1: "x", fld2: 43 }, { fld1: "r", fld2: 80 } ]  }

I created an index on arr.fld1; this is a Multikey index (indexes on arrays are called as so). The _id field already has the default unique index.

The following query uses the indexes on both fields - arr.fld1 and the _id. The query plan generated using explain() on the query showed an index scan (IXSCAN) for both fields.

db.test.find( { $or: [ { _id: 2 }, { "arr.fld1": "m" } ] } )

Now the same query filter is used for the update operation also. So, the update where we add two sub-documents to the array:

db.test.update(
  { $or: [ { _id: 1 }, { "arr.fld1": "m" } ] },
  { $addToSet: { arr: { $each: [ { "fld1": "xx" }, { "fld1": "zz" } ] } } }
)

Again, the query plan showed that both the indexes are used for the update operation. Note, I have not used the hint for the find or the update query.

I cannot come to conclusion about what the issue is with your code or indexes (see point Notes: 1, below).

NOTES:

The above observations are based on queries run on a MongoDB server version 4.0 (valid for version 3.6 also, as I know).
The explain method is used as follows for find and update: db.collection.explain().find( ... ) and db.collection.explain().update( ... ).
Note that you cannot generate a query plan using explain() for updateOne method; it is only available for findAndModify() and update() methods. You can get a list of methods that can generate a query plan by using the command at mongo shell: db.collection.explain().help().

Note on Java Code:

The Java code to update an array field with multiple sub-document add, is as follows:

collection.updateOne(
    or(eq("_id", new Integer(1)), eq("arr.fld1", "m")),
    addEachToSet("arr", Arrays.asList(new Document("fld1", "value-1"), new Document("fld1", "value-2"))
);

Set hint for update to use indexes

1 Answers1