Query is not covered anymore after upgrade to Mongo 6

Working with Data
,
1

We have a collection with 3 relevant fields: ID, time and a list of codes.
Our index has all 3 relevant fields indexed as {some_id: 1, time: -1, codes: 1}

Before upgrading to Mongo 6, the explain to the following query returned PROJECTION_COVERED.
Since the upgrade the query is no longer covered for some reason. Explain gives PROJECTION_DEFAULT and FETCH.
What happened? How can we fix it?

[
            {"$match": {"id": some_id}},
            {"$unwind": "$codes"},
            {"$group": {"_id": "$codes", "time": {"$max": "$time"}}}
]

Thanks in advance.

2

Hi @SchiaviniUM

The newest Mongo version is Mongo 7.

It’s began after an upgrade to Mongo 6 or to Mongo 7? I am asking for the version because I have a similar issue here after upgrading to Mongo 7 and I solved it with the hint.

Can you try to use hint to see if your performance gets better?

3

May be it is a bad cut-n-paste from your part but you $match on the field id but this field is not in the index. So if your code is really like the one you shared and the index really the one you shared then I am pretty sure that the query is not covered even in the previous version. Perhaps what you really want is

{"$match": {"some_id": id}},

rather than

4

some_id is correct and that’s in the index {some_id: 1, time: -1, codes: 1}
The query had a paste typo though, I meant {"$match": {"some_id": some_id}},

This started happening since v6.
Using hint does not work as the query already does use the index. It just has an extra FETCH stage that should not be needed.