Unique Index with partial filter is not being used by MongoDB

Working with Data
1

Hi,

I have a table called website and its url field could be null or a string type, and I created an unique index on it with the partialFilter:

db.website.createIndex(
    {"url": 1}, 
    {unique: true, partialFilterExpression: {url: {$type: "string"}}}
);

However, when I do a query like:

db.website.find({url: "mongo"}).explain()

I can see MongoDB chose to use COLLSCAN instead of using the index IXSCAN. In my case because the url value I passed is a string, and it should be covered by the index. Why does mongo choose to do a full COLLSCAN ?

Thank you in advance

2

Hi,
Try changing your query to:

db.website.find({url: {$type: "string", $eq: "mongo"}})

Thanks,
Rafael,

2 Likes
3

Thank you Rafael, that works.

However, I am wondering why do we need to pass the type explicitly. MongoDB should be start enough to infer that the type of “mongo” is a string, because on the above unique key example, if we do the insertion:

db.website.insert({url: "mongo"})

MongoDB can infer the type of “mongo” is a string because it needs to ensure the key’s uniqueness. If MongoDB can infer the type for insertion, why can’t it infer the type for querying?

4

Hi @Aaron_Febo ,
When you run this query:

db.website.find({url: "mongo"})

the results may include documents with url as a Symbol, which is not covered by your index. For that reason, MongoDB driver will not use the index in this case.

from the docs:

MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.

To test it, you can run this from the shell:

db.website.insertOne({url: BSONSymbol("mongo")})
db.website.find({url: "mongo"})

Btw, instead of changing your query, you can delete your index and create is as follows:

db.website.createIndex(
    {"url": 1}, 
    {unique: true, partialFilterExpression: {url: {$gte: ''}}}
);

See Partial Index with Unique Constraint for more information.

Thanks,
Rafael,

1 Like
5

that’s a very clear explanation, thank you @Rafael_Green !

2 Likes
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.