M121 Final question 5 - some guidence?

MongoDB University M121: The MongoDB Aggregation Framework
#1

Hello,

I’m rather going round in circles on this question.

The lecture in Chapter 6, Aggregation performance, gives some explicit techniques for optimizing a pipeline.

The lectures on pipeline optimization explore the “explain” option and show how the MongoDB pipeline optimizer works.

Applying these techniques to some of the possible solutions for this question it can be seen that the optimizer elevates $match and $sort queries to the front of the pipeline to take advantage of the indexes. The optimizer will also split compound $match statements if part of the $match can use an index.

The resultant ‘plans’ are so similar that I’m finding it next to impossible to ascertain which is the ‘best’

Can anyone point me to additional resources that might help, please?

#2

@astebbing

First of all, I’d suggest that you focus on how you can optimize the pipeline, rather than how the query optimizer does it – notice that you do not have a ‘cities’ collection in the database as given, and creating your own is probably likely to introduce some confusion IMO.

Also notice that the two indexes you have (‘city_1’ and ‘country_1’) are single field indexes, and so you’re only going to be able to use one or the other of these on the pipeline. Once one of them is applied, the other won’t be useful any more.

So start with the question to yourself: “what is the best use that my pipeline can make of the available indexes”? Good luck.

#3

Thank you @DHz.

Also notice that the two indexes you have (‘city_1’ and ‘country_1’) are single field indexes, and so you’re only going to be able to use one or the other of these on the pipeline. Once one of them is applied, the other won’t be useful any more.

That helps. I must have missed that little gem along the way.

#4

@astebbing

I’m not sure it’s mentioned – and I don’t think it’s obvious offhand. But look at how an index is built and you’ll see how this has to be true. Remember that a single field index will have the key field and a pointer to the matching document in the collection – and nothing else. So that if you use (say) an index for $match, once that has been applied, no other index can help you since the other index(es) won’t have that field, (Hence the use of compound indexes :slightly_smiling_face: ). The documents retrieved will have that field, but the pointer in any associated index will point back to the base collection, not your new set of documents, and if you use that pointer, you lose your match results. So that’s not any help, right? :wink:

#5