Easy criterion for "Covered Queries" (replacement for deprecated indexOnly)

Working with Data
#1

Hi all,

Q: what is the definite and reliable way in MongoDB 4.x to figure out whether a query is covered by an index ?

In earlier MongoDB versions this important question was easy to answer via checking ‘indexOnly’ boolean key of the explain() dictionary.

I’ve walked through different sources to answer this question and the only answer I found is:
https://docs.mongodb.com/manual/reference/explain-results/#covered-queries

However neither the answer is unambiguous nor it sounds like be a straight forward to implement for an arbitrary query. If it plays any role, I am on pymongo.

Thank you in advance for answers and ideas!
kind regards

Valery

#2

Hi Valery,

The easiest way to determine if a query is fully covered is to use db.collection.explain('executionStats').find(...) in the mongo shell.

For example, if a query is not covered:

	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 10,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 10,
		"totalDocsExamined" : 10,

note that totalKeysExamined and totalDocsExamined are both 10. Meaning that the server must examine 10 documents to return the query.

If it’s covered:

	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 10,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 10,
		"totalDocsExamined" : 0,

Note that totalKeysExamined is 10, but totalDocsExamined is 0. Meaning that the server did not need to examine any documents, and can answer the query from index scan alone. A covered query will have totalDocsExamined: 0 with a non-zero totalKeysExamined in the explain output.

Additionally, if you’re using MongoDB 4.2, you should see a PROJECTION_COVERED stage:

		"executionStages" : {
			"stage" : "PROJECTION_COVERED",

Best regards,
Kevin