M201 Lab 2.1: Using Indexes to Sort - new mongodb versions seems to render this quiz invalid

[Yilmaz_Durmaz (Yılmaz Durmaz)]

I get my first fail in all the developer path courses because I am getting IXSCAN from a query that is told otherwise.

• db.people.find({ "first_name": { $gt: "J" } }).sort({ "address.city": -1 })

No, this query doesn’t use equality on the index prefix. When using an index for filtering and sorting the query must include equality conditions on all the prefix keys that precede the sort keys. Moreover, on the sort predicate it skipped the next key in the prefix “address.state”.

I have switched to mongoDB 5.0.4 and this is what this query’s explain returns.

{
        "explainVersion" : "1",
        "queryPlanner" : {
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "first_name" : {
                                "$gt" : "J"
                        }
                },
                "queryHash" : "602B7D73",
                "planCacheKey" : "3694C554",
                "maxIndexedOrSolutionsReached" : false,
                "maxIndexedAndSolutionsReached" : false,
                "maxScansToExplodeReached" : false,
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "SORT",
                                "sortPattern" : {
                                        "address.city" : -1
                                },
                                "memLimit" : 104857600,
                                "type" : "default",
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "first_name" : 1,
                                                "address.street" : -1,
                                                "address.city" : -1,
                                                "ssn" : 1
                                        },
                                        "indexName" : "faas",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "first_name" : [ ],
                                                "address.street" : [ ],
                                                "address.city" : [ ],
                                                "ssn" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "first_name" : [
                                                        "(\"J\", {})"
                                                ],
                                                "address.street" : [
                                                        "[MaxKey, MinKey]"
                                                ],
                                                "address.city" : [
                                                        "[MaxKey, MinKey]"
                                                ],
                                                "ssn" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "command" : {
                "find" : "people",
                "filter" : {
                        "first_name" : {
                                "$gt" : "J"
                        }
                },
                "sort" : {
                        "address.city" : -1
                },
                "$db" : "m201"
        },
        "serverInfo" : {
                "host" : "Souls-LX",
                "port" : 27017,
                "version" : "5.0.4",
                "gitVersion" : "62a84ede3cc9a334e8bc82160714df71e7d3a29e"
        },
        "serverParameters" : {
                "internalQueryFacetBufferSizeBytes" : 104857600,
                "internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
                "internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
                "internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
                "internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
                "internalQueryProhibitBlockingMergeOnMongoS" : 0,
                "internalQueryMaxAddToSetBytes" : 104857600,
                "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
        },
        "ok" : 1
}

by the way, there is no “address.state” in this collection and I used “address.street” instead. however, it does not affect the result because query does not use it.

[steevej (Steeve Juneau)]

@Yilmaz_Durmaz

there is no “address.state” in this collection

If you are referring to the people collection supplied in the course. Some documents have a field named address.state, for example _id 57d7a121fa937f710a7d487b.

@Yilmaz_Durmaz

I used “address.street” instead

You changed the problem statement. That is why you failed. The problem statement started with

Given the following index:

{ “first_name”: 1, “address.state”: -1, “address.city”: -1, “ssn”: 1 }

Which of the following queries are able to use it for both filtering and sorting?

You do not change the problem statement to match your understanding.

@Yilmaz_Durmaz

query does not use it

Exactly, that is the key point that had to be learn. If a query does not use one of the key prefix it cannot use the following keys to sort. The field state comes before the field city in the index and state is not used in the query then the index cannot be used to sort on city.

Hence the description of the detailed answer:

@Yilmaz_Durmaz

When using an index for filtering and sorting the query must include equality conditions on all the prefix keys that precede the sort keys. Moreover, on the sort predicate it skipped the next key in the prefix “address.state”.

[Yilmaz_Durmaz (Yılmaz Durmaz)]

@steevej

{ “first_name”: 1, “address.state”: -1, “address.city”: -1, “ssn”: 1 }

if I use “address.street” instead of “address.state” in all queries consistently, the result should not change. otherwise whole database system will collapse.

as for the actual problem: it is not clearly told in the videos, nor the statement given to be explanation. “equality” of what? the choice of words are just leading to confussion. as if someone wrote this in greek, then translated to chineese then someone else tried to translate it to english.

there is also seemingly changed keywords in the output. I have seen some other posts using “memory” keyword in the sort stage, which would tell me it is not taken from the index but rather sorted again in memory.

after few days of struggling, I would now use “as long as the arrangement of the indexed keys as well as their sort orders (asc/desc) are preserved, then IXSCAN is used”.

[Kothan_Jayagopal]

I am stumbled… i didn’t use the lab. I answered just by looking at it. Here are the two things i could not understand from answers provided:

1. is Mongodb can use only equality in the filter clause to make use of Index?
2. If i am correct, course lecture said order matters from the filter predicate to the sort operator. However detailed answers says that ‘order of the fields in the query predicate does not matter’.