Mongodb Query Doesn't Use Index

user_2441351 0
Viewed 2931

I have a compound index set up on a mongo collection, and no matter how specifically I target the prefix, I can't get the query to use it.

How index was created:

db.collection.createIndex({"insert_time":-1,"name":"text"},{background: true})

Index specification output:

db.collection.getIndexes()
[
{
    "v" : 2,
    "key" : {
        "_id" : 1
    },
    "name" : "_id_",
    "ns" : "db.collection"
},
{
    "v" : 2,
    "key" : {
        "insert_time" : -1,
        "_fts" : "text",
        "_ftsx" : 1
    },
    "name" : "insert_time_-1_name_text",
    "ns" : "db.collection",
    "background" : true,
    "weights" : {
        "name" : 1
    },
    "default_language" : "english",
    "language_override" : "language",
    "textIndexVersion" : 3
}
]

But when I run even the simplest query on insert_time, explain() shows me that the winning plan is to do COLLSCAN, and not use any index:

db.collection.find({"insert_time": ISODate("2018-08- 
05T19:00:00Z")}).explain()
{
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "db.collection",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "insert_time" : {
            "$eq" : ISODate("2018-08-05T19:00:00Z")
        }
    },
    "winningPlan" : {
        "stage" : "COLLSCAN",
        "filter" : {
            "insert_time" : {
                "$eq" : ISODate("2018-08-05T19:00:00Z")
            }
        },
        "direction" : "forward"
    },
    "rejectedPlans" : [ ]
},
"serverInfo" : {
    "host" : "foo",
    "port" : 0000,
    "version" : "3.4.10",
    "gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
},
"ok" : 1
}

Even though the index is specified as a "compound index", my understanding is that I should be able to search by a "prefix", which in this case, insert_time is. Any ideas as to why mongo won't use my index?

mongodb
2 Answers

text index is sparse by default.

And non-text index field in index does not reference document in text index (reference)

For a compound index that includes a text index key along with keys of other types, only the text index field determines whether the index references a document. The other keys do not determine whether the index references the documents or not.

=> When use prefix index with out text index field, the result may incomplete

About this case (sparse Index and Incomplete Results), MongoDB states that:

If a sparse index would result in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a hint() explicitly specifies the index.

If you use hint('yourTextIndexName') then the index is used.

So OP's case is an expected behaviour, but is not very well documented.

user_84865180

This feels like a bug.

I'm able to reproduce the behavior you're seeing on my own dataset, with MongoDB 3.6.7. I hinted the index in question and the query planner was unable to bound on the prefix field (I was doing a direct match on year): 

"indexBounds" : {
                    "year" : [
                        "[MinKey, MaxKey]"
                    ],
                    "_fts" : [
                        "[MinKey, MaxKey]"
                    ],
                    "_ftsx" : [
                        "[MinKey, MaxKey]"
                    ]
                },

The documentation for compound text indexes does not contain any statements indicating it is not possible to use the prefix portion of a text index. The documentation on index prefixes also do not contain any language regarding a restriction.

EDIT:

I created a server ticket to report this behavior and try to get more information on if this is expected or a bug.

user_10927670
Related