Covered query PROJECTION_SIMPLE vs PROJECTION_COVERED

MongoDB University M201: MongoDB Performance
#1

Lesson 4.3 on Covered Queries shows a projection whose projected fields are all “covered” in the index used for the query. The executionStats indicates PROJECTION_COVERED. This is straightforward.

I I remove “_id”: 0 from the projection (thus including _id) in the projection, the executionStats now indicates PROJECTION_SIMPLE, and contains a non-zero totalDocsExamined, indicating that the projection could not be satisfied by the index alone.

Here’s my question:

It seems to me that a very (the most?) common use case for a projection query would be to display the result set to the user in some sort of selector. One way I thought of to do this is to include _id as the last field in the index, e.g. {name: 1, cuisine: 1, stars: 1, _id: 1}.

Is this the suggested way of accomplishing this, or is there some better approach to getting a covered projection query that includes _id?

#2

@Rick_47429 if you come from a SQL background, $project is somewhat equivalent to a SELECT clause; with the exception of the flexibility that comes with $project when it comes to field exclusions. So whatever you use SELECT for is a similar use case for $project.

In your example, I can think of two ways of accomplishing this:

  • Create a view that excludes _id and includes all the fields from the index, then call the view for subsequent queries. This way, you won’t even need to specify a $project stage when executing the view. Note, it won’t be covered if you don’t exclude _id and include only relevant index fields.
  • Educate your Developers to always exclude _id and include the relevant fields from the index

The former would be my preferred option.

#3

By the way did you mean “exclude” or “include” here? If for some reason, you must include _id as part of the results, then your approach is the only way to make it covered.

#4

I did mean “include”. Thanks for confirming the approach that I indicated in my previous message, i.e. including _id as the last field of the index.

#5

It’s unusual to include a monotonic _id field in your resultset, but I’m sure you have your reasons. :+1:

#6

Well, maybe I’m on the wrong track here, but here’s an example of why I think including _id in the projection is worthwhile. Suppose I have a collection of restaurants and I let the user search for restaurants nearby with a given cuisine and rating and with the results, select a restaurant to get a detailed view. It seems that the most natural approach to this would be to return _id with each restaurant returned by the first search and then read the full restaurant document with the _id of the restaurant selected by the user. Do you have a different take on this? Thanks.

#7

I think $facet would be useful here. It allows you to run multiple aggregations on one collection in one fell swoop. Also, the way you model your schema would also have an impact.

#8

Am I correct that $facet would require reading the documents and would not be able to use a covered projection? The reason I was suggesting including _id in the index was to allow a query to use a covered projection to maximize performance and minimize load.

#9

Faceted searches are like running mini aggregation pipelines. All the result sets from a $facet can be covered if the aggregation pipelines are covered. The unique thing here also is that you are hitting the collection once. However, if the menu and the restaurant details all reside in the same collection, you just need to query it once with the necessary details included, then let your UI present the relevant sections.

#10

OK, so $facet can perform covered. Good. I don’t really understand the point you are making with menu and restaurant. The example that I had was a query to get a restaurant list to present to the user for selecting a restaurant, and then using the user’s selection (_id) to get the full document for that restaurant. What would such a scenario look like using $facet? Thanks again.

#11

It’s Sunday afternoon here and I was thinking about lunch :smiley:

Ok. I would imagine that Cuisine, Rating and Restaurant details are all in one collection? Your “covered index” would be cuisine, rating, [restaurant name, address, number, email, etc… bare minimum info]. Similar to the way Google does it, your UI will show a collapsed list of [restaurant names], expanding the frame will reveal [restaurant address, number, email, etc].

#12

Got it. Thanks again, 007. Appreciate the time you’ve taken to explain.

#13

You’re welcome @Rick_47429!