Sorry for reposting. I accidentally deleted my old post and couldn’t find the way to undo that.
I have a nullable string field, and when the value is not null, it needs to unique.
I created the index using pymongo like this
f = "item_number"
my_coll.create_index(
[(f, 1)], unique=True, name=f"unique_{f}",
partialFilterExpression={f: {"$exists": True, "$type": "string"}},
)
The index could be created successfully, and I inserted documents to the collection.
When I tried to search for an item_number, COLLSCAN is always used.
My search is like this:
db.my_coll.find({item_number: "2ae9dc2f-2ff6-4239-9b7a-cccb757fc912"}).explain("executionStats")
The execution plan is attached. (
mongo_plan.json (2.5 KB)
)
Have I done something wrong? Should I expect IXSCAN to be used for such search?
Thank you very much.
1 Like
Hi @Averell_Tran ,
This is an interesting result! I thought the same as you - that it would use the index.
Reading up on the docs, it still isn’t completely clear why the index isn’t being chosen in this case - it’s a bit ambiguous.
I’ve found that a more specific query matching the partialFilterExpression does use the index:
db.my_coll.find({
"item_number": {
"$eq": "2ae9dc2f-2ff6-4239-9b7a-cccb757fc912",
"$type": "string",
},
})
That query, specifying the type as well as the value, results in an IXSCAN, as you’d expect.
I think if you want the query to use the index without the extra type check, then you probably want to remove the type requirement from your index’s partialFilterExpression.
Hope this helps!
Mark
1 Like
Thank you, Mark.
I had to have that type
requirement in the partialFilterExpression
because I wanted to allow multiple documents having item_number = null
. Without that requirement, documents with null will also be indexed and that unique
condition would apply.
I’m trying to change to {f: {"$exists": True, "$gt": ""}}
, which looks awkward, but it seems working for me for now.
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:
- Update the query from
$exists: true
to $gt: ''
, as suggested by @Averell_Tran
- 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.