Jul 2024
2 / 4
Jul 2024
Feb 6
Averell_Tran
Jul 2024

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.
Mark_SmithMongoDB Employee
Jul 2024

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

Averell_Tran
Jul 2024

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.

6 months later
Alec_Gibson
Feb 6

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.

Want to read more? Browse other topics in Working with Data or view latest topics.