why my query is not covered by the index with collation?

user_1261464 0
Viewed 153

i have the following collection:

{ "_id" : ObjectId("61b75cd077a764166f282d24"), "name" : "topolino" }
{ "_id" : ObjectId("61b75cdd77a764166f282d25"), "name" : "tòpolino" }

and the following indexes (notice that i set up a default collation at the collection level)

 db.collation.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "collation" : {
                        "locale" : "it",
                        "caseLevel" : false,
                        "caseFirst" : "off",
                        "strength" : 1,
                        "numericOrdering" : false,
                        "alternate" : "non-ignorable",
                        "maxVariable" : "punct",
                        "normalization" : false,
                        "backwards" : false,
                        "version" : "57.1"
                }
        },
        {
                "v" : 2,
                "key" : {
                        "name" : 1
                },
                "name" : "name_1",
                "collation" : {
                        "locale" : "it",
                        "caseLevel" : false,
                        "caseFirst" : "off",
                        "strength" : 1,
                        "numericOrdering" : false,
                        "alternate" : "non-ignorable",
                        "maxVariable" : "punct",
                        "normalization" : false,
                        "backwards" : false,
                        "version" : "57.1"
                }
        }
]

then i run "explain" on the following query:

db.collation.find({name: "topolino"},{_id : 0, name:1}).sort({name:1}).explain("executionStats");

i expected the query to be covered, since it searches only the "name" field and i explicitly removed the "_id" field. But the explain method shows:

 "totalKeysExamined" : 2,
 "totalDocsExamined" : 2,

which means that it still needs a FETCH step after the IXSCAN

Anybody can explain this behaviour? Thanks

mongodb
1 Answers

Without collation it will work, with 0 docs examined.
But you are using collation "it".

The index can say that it matches, but it doesn't really know the real value that it stored in the document, until it reads it.

I am not sure about this, but it looks logical to need to examine the docs also, because collation indexes don't have 1-1 matches to know the real stored value also.

This is expected with all indexes that can match 1 to many possible values.

Multikey indexes queries are not covered also, because the index, don't know the order of the members in the array, so again the docs are examined.

user_48826920
Related