Recently I encountered an issue for which I haven’t found an explanation as yet. I have a collection with a simple one property index. The collection is not huge, only few thousands of documents. The issue I encountered is that when some of the documents grow in size, I’m assuming we are close to the document size limit, the index starts performing very poorly. When I use the explain() cursor method with
executionStats mode I can see that the winning plan is opting for the index and the result is pretty fast as expected. However this is not the case when I run the query in mongoshell or some other DB tool. When doing so, the index is either not being used or performs very poorly. On the other hand when I reduce the documents size to a decent size the query returns efficiently. Can someone explain why this is so please? To my knowledge (or at least in other databases) an index is pointer based so it should perform the same irrespective of document size. Any insight would be highly appreciated. Thank you.