0

I have created the following 1000 documents:

for(i=0;i<1000;i++){db.doc.insert({"doc":{"k1":"v_"+i,"k2":i},"scal":i})}

So a document looks like this:

var d1 = db.doc.findOne()
{
    "_id" : ObjectId("5ce25f8920b0be2428648e38"),
    "doc" : {
        "k1" : "v_0",
        "k2" : 0
    },
    "scal" : 0
}

I've created the following two indexes:

db.doc.createIndex({"doc":1})
db.doc.createIndex({"scal":1})

I wonder why the following query is not covered:

db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 999,
        ...
}

However, the same query using a scalar instead of a document is covered:

db.doc.explain(true).find({scal:{$gt:d1.scal}},{_id:0,scal:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 0,
        ...
}

I only get the query covered on documents, when I'm not using a range operator:

db.doc.explain(true).find({doc:d1.doc},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 0,
        ...
}

Even $in would result in a covered query:

var dis = db.doc.distinct("doc")
db.doc.explain(true).find({doc:{$in:dis}},{_id:0,doc:1})
{
...
"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1000,
        "executionTimeMillis" : 8,
        "totalKeysExamined" : 1000,
        "totalDocsExamined" : 0,
        ...
}

However range operators such as $gt seem not to be able to result in a covered query when applied to documents. Is this an expected behaviour which is documented anywhere or is it a bug?

I'm using a replSet running with mongodb v3.6 Linux 64 Bit.

0
db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})

The above query doesn't use db.doc.createIndex({"doc":1}) index, because the index is created at embedded document level. To use this index, you should specify all fields in the document. For example

db.doc.explain(true).find({"doc" : { "k1" : "v_0", "k2" : 0 }},{_id:0,doc:1})

or else you need to create an index at field level for embedded documents as shown below

db.doc.createIndex({"doc.k1":1});
db.doc.createIndex({"doc.k2":1});

For more details refer

Create an Index on an Embedded Field

Create an Index on Embedded Document

  • Thanks for your answer but I think you are mostly wrong. You said I should specify all fields in the doc but I exactly did this by writing d1.doc (d1.doc corresponds to { "k1" : "v_0", "k2" : 0 }). Furthermore, creating both your suggested indexes won't result in a covered query. The point is, that I'm using a range operator but your query uses a equals operator, hence your query is covered but not mine. However, you're right that the problem relies in indexing at document level. Index values should be scalar values and not documents. See same question: tinyurl.com/y44aevmk – Kay May 22 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.