Partial index is not used during search

Working with Data
,
4

Sorry to necropost, but I just ran into this as well and was very surprised at how hard I had to work to get MongoDB to use the index without ``.hint()`.

The query I was trying to optimize was this:

db.collection.countDocuments({
  ack: {$exists: true},
  visible: {$gt: '2025-01-01'},
});

I originally tried this index:

db.collection.createIndex({ack: 1, visible: 1}, { partialFilterExpression: { ack: {$exists: true}, }, });

But that didn’t work for some reason.

Eventually I had to:

  1. Update the query from $exists: true to $gt: '', as suggested by @Averell_Tran
  2. Swap the order of my index keys

So the final index was:

``
db.collection.createIndex({visible: 1, ack: 1}, {
partialFilterExpression: {
ack: {$exists: true},
},
})
`

And the final query:

db.collection.countDocuments({ ack: {$gt: ''}, visible: {$gt: '...'}, });

Some things I found surprising:

  • I had to include the ack in the index keys, even though partial indexes support querying not on this
  • I couldn’t use $exists in the query
  • I had to swap the key order in the index. I guess this makes sense now I think about it. I originally had ack first because querying on its absence usually allows a range lookup on the index, but that’s not the case here.