I have a collection of about 1.2 million records.
On this collection, I have a sparse index that looks like {A: 1, B: 1, C: 1}. A is the field that not all documents will have; B is a true/false value; and C is a Date datatype.
My query is the following pattern: {A: {$exists:true}, B: false, C: {$lt: new Date(<some timestamp>)}}
I’m seeing in our MongoDB logs that this is examining 575k keys and almost 300k documents. If I change the index to a partial index with the filter A: {$exists:true}, I’m getting much better query targeting.
Does anyone know why this is the case?