Why a query is not covered when using a range operator on documents?

Question

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.

score 0 · Answer 1 · answered May 20 '19 at 18:10

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

Why a query is not covered when using a range operator on documents?

1 Answers1