LAB 2.1 Using Indexes to sort, the answer I chose was wrong but its right

MongoDB University M201: MongoDB Performance
#1

I ran execution stats on this query:

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

It wasn’t a pretty index by no means but it wasn’t a collection scan either.

here are the results. I believe there should be credit given for this answer. Also, the lessons to the lab are very confusing. I though indexing had to correlate to the order it was given. many of these orders didn’t make sense.

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "first_name" : {
                                "$gt" : "J"
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "address.city" : -1
                        },
                        "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "first_name" : 1,
                                                        "address.state" : -1,
                                                        "address.city" : -1,
                                                        "ssn" : 1
                                                },
                                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "first_name" : [ ],
                                                        "address.state" : [ ],
                                                        "address.city" : [ ],
                                                        "ssn" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "first_name" : [
                                                                "(\"J\", {})"
                                                        ],
                                                        "address.state" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "address.city" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "ssn" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 32023,
                "executionTimeMillis" : 334,
                "totalKeysExamined" : 32023,
                "totalDocsExamined" : 32023,
                "executionStages" : {
                        "stage" : "SORT",
                        "nReturned" : 32023,
                        "executionTimeMillisEstimate" : 319,
                        "works" : 64049,
                        "advanced" : 32023,
                        "needTime" : 32025,
                        "needYield" : 0,
                        "saveState" : 507,
                        "restoreState" : 507,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "sortPattern" : {
                                "address.city" : -1
                        },
                        "memUsage" : 12672397,
                        "memLimit" : 33554432,
                        "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "nReturned" : 32023,
                                "executionTimeMillisEstimate" : 212,
                                "works" : 32025,
                                "advanced" : 32023,
                                "needTime" : 1,
                                "needYield" : 0,
                                "saveState" : 507,
                                "restoreState" : 507,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "nReturned" : 32023,
                                        "executionTimeMillisEstimate" : 159,
                                        "works" : 32024,
                                        "advanced" : 32023,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 507,
                                        "restoreState" : 507,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "docsExamined" : 32023,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 32023,
                                                "executionTimeMillisEstimate" : 95,
                                                "works" : 32024,
                                                "advanced" : 32023,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 507,
                                                "restoreState" : 507,
                                                "isEOF" : 1,
                                                "invalidates" : 0,
                                                "keyPattern" : {
                                                        "first_name" : 1,
                                                        "address.state" : -1,
                                                        "address.city" : -1,
                                                        "ssn" : 1
                                                },
                                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "first_name" : [ ],
                                                        "address.state" : [ ],
                                                        "address.city" : [ ],
                                                        "ssn" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "first_name" : [
                                                                "(\"J\", {})"
                                                        ],
                                                        "address.state" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "address.city" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "ssn" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 32023,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0
                                        }
                                }
                        }
                }
        },
        "serverInfo" : {
                "host" : "Onyx-Jaguar",
                "port" : 27017,
                "version" : "4.0.6",
                "gitVersion" : "caa42a1f75a56c7643d0b68d3880444375ec42e3"
        },
        "ok" : 1
}
#2

@xtianus

The explain() output clearly shows that the index is not being used for both the query and the sorting stages, which is the task in the lab.

If you are confused by the lectures, I would suggest going back over them and reviewing any that are confusing. If you continue to have questions or problems, open a new thread here with a specific question and we’ll try to help.

#3

So I should be looking at explain? Lol fine let me go back and look it over

#4

@xtianus

You might review the documentation on the cursor.explain() output here, particularly the section on the “Sort Stage”.

1 Like
#5

sorry, i think i read into too much the returned documents vs the scanned documents. it was kind of close to a 1 to 1 ratio… clearly it was a sort scan however.

#6