I am using MongoDB 6.0. I have a collection with millions of documents. I have to run certain queries on this collection.
I have created indices that support fast query and counting.
The problem is that I also have a low cardinality field (visible of type boolean), which I also have to support for querying, but only with visible: true (I never have to run a query with visible: false).
My idea was to create the same indices that I use without the visible field, and for this I used partial indices with same keys and a filter expression:
{partialFilterExpression: {"visible": true}}
Note that "visible" is not added to the index keys, it's just part of the partialFilterExpression.
My thoughts were that the same queries with "visible": true would have no performance penalty, if anything, they should run faster as they (should) use an index that has less entries (only documents where visible=true).
But these queries (especially the counting) turn out to be much slower.
I used explain() to see if the proper indices are used, and often they weren't. So I used hint to ensure they are, which explain() also confirmed, but the queries are still much slower.
What could be the cause? How can I ensure that queries and counting with visible: true run as fast as queries without the visible filter?
You're running into a current limitation of the database, a missing optimization at its core. This question is effectively a duplicate of Covered Queries do not work with partial indices. If you follow the links, you end up at ticket SERVER-28889 tracking the request.
It looks like this improvement will be available in an upcoming version of MongoDB. In the meantime the workaround would be to also add the field as a key to the index (along with it being in the partial filter expression).