Mongodb lookup aggregation pipeline index performance

[mdewit]

Hi all. I have a question or two about the mongodb lookup aggregation performance.

Using this aggregation to lookup from another collection, I found through testing that indexing the field used of the collection being joined on (the foreignField field in the lookup request) can improve performance a lot. Note that I couldnt find any documentation to confirm this although practically it makes a huge difference in my query (if there are any docs that confirms this, please let me know!).

Now, for 3.6 they have also added "pipeline" field which, if specified, is ran on the joined collection. This pipline can contain match and projection stages, maybe even more.

My questions is: if specifying the pipeline in the lookup aggregation, will the indexes defined for the joined collection be used when processing this pipeline? It would be great if I can create a covered query inside this lookup pipeline by using the correct match & project stages (which should result in better performance than when only matching/projecting after the lookup has been completed)

Many thanks!

[Marius De Wit]

So after some experimentation on my part (by adding and viewing index statistics each time after querying), I have found the following:

1) When using lookup by specifying the foreignField of the target collection to look up, if an index exist on this foreign field then it would be used. 

2) When using lookup by specifying a pipeline, all fields in the pipeline match stage matched with the equality ($eq) operator would be used. Sadly, other matches with operators such as $gt, $lt, $gte etc does *not* cause an index to be used.

3) When using lookup by specifying a pipeline, it is not possible to use a projection in the pipeline to have the query engine pick a more specific index in order to create a covered query.

I would now like to know, is improving 2) and 3) perhaps planned for a future release, as I think it could possibly make lookups much quicker?

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/748b649d-9a97-4400-b5df-22e26aa54787%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.