Lab 2.1: Using Indexes to Sort

Index details:

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

Queries:

db.people.find({ “first_name”: “Jessica”, “address.state”: { $lt: “S”} }).sort({ “address.state”: 1 })
db.people.find({ “first_name”: “Jessica”, “address.state”: “South Dakota” }).sort({ “address.state”: 1 })

How the first query uses the index for both filtering and sorting but the second query is using the index only for filtering?

My guess is that since the second one has only 1 ‘address.state’ there is nothing to sort. There is nothing to sort therefore it does not use the index to sort. The output of explain() would be nice to have.

I tried it but it confused me.

It is funny because I don’t see any sort stage in the first case. Maybe because it know all documents are already in the desired order after the FETCH using an IXSCAN. In the second one I see a SORT stage but it uses the index. I am more confused.

Any way , in both case it looks efficient. The 2nd one looks at 9 keys, 9 documents and returns 9 docs. The 1st one looks at 206 keys, 206 documents and returns 206 documents.

1 Like

Hi Steevej, Thanks for your reply.

How about this case?

I created a new index

{ employer: 1}

Query:

db.people.find({ employer: “Underwood LLC” }).sort({ employer: 1}).explain()

This query also has only one employer with the name “Underwood LLC”, but it uses the index for both filtering and sorting.

Thanks,
Mani P.

In that case, please show us the explain() output for both cases.

This is an interesting question :slight_smile:

db.people.find({ “first_name”: “Jessica”, “address.state”: { $lt: “S”} }).sort({ “address.state”: 1 }).explain();

/* 1 */
{
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “m201.people”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“first_name” : {
“$eq” : “Jessica”
}
},
{
“address.state” : {
“$lt” : “S”
}
}
]
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“first_name” : 1,
“address.state” : -1,
“address.city” : -1,
“ssn” : 1
},
“indexName” : “test”,
“isMultiKey” : false,
“multiKeyPaths” : {
“first_name” : ,
“address.state” : ,
“address.city” : ,
“ssn” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “backward”,
“indexBounds” : {
“first_name” : [
“[“Jessica”, “Jessica”]”
],
“address.state” : [
“[”", “S”)"
],
“address.city” : [
“[MinKey, MaxKey]”
],
“ssn” : [
“[MaxKey, MinKey]”
]
}
}
},
“rejectedPlans” :
},
“serverInfo” : {
“host” : “A5943425”,
“port” : 27017,
“version” : “4.0.3”,
“gitVersion” : “7ea530946fa7880364d88c8d8b6026bbc9ffa48c”
},
“ok” : 1.0
}

db.people.find({ “first_name”: “Jessica”, “address.state”: “South Dakota” }).sort({ “address.state”: 1 }).explain();

/* 1 */
{
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “m201.people”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“address.state” : {
“$eq” : “South Dakota”
}
},
{
“first_name” : {
“$eq” : “Jessica”
}
}
]
},
“winningPlan” : {
“stage” : “SORT”,
“sortPattern” : {
“address.state” : 1.0
},
“inputStage” : {
“stage” : “SORT_KEY_GENERATOR”,
“inputStage” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“first_name” : 1,
“address.state” : -1,
“address.city” : -1,
“ssn” : 1
},
“indexName” : “test”,
“isMultiKey” : false,
“multiKeyPaths” : {
“first_name” : ,
“address.state” : ,
“address.city” : ,
“ssn” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“first_name” : [
“[“Jessica”, “Jessica”]”
],
“address.state” : [
“[“South Dakota”, “South Dakota”]”
],
“address.city” : [
“[MaxKey, MinKey]”
],
“ssn” : [
“[MinKey, MaxKey]”
]
}
}
}
}
},
“rejectedPlans” :
},
“serverInfo” : {
“host” : “A5943425”,
“port” : 27017,
“version” : “4.0.3”,
“gitVersion” : “7ea530946fa7880364d88c8d8b6026bbc9ffa48c”
},
“ok” : 1.0
}

db.people.find({ employer: “Underwood LLC” }).sort({ employer: 1}).explain();

/* 1 */
{
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “m201.people”,
“indexFilterSet” : false,
“parsedQuery” : {
“employer” : {
“$eq” : “Underwood LLC”
}
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“employer” : 1
},
“indexName” : “employer_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“employer” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“employer” : [
“[“Underwood LLC”, “Underwood LLC”]”
]
}
}
},
“rejectedPlans” :
},
“serverInfo” : {
“host” : “A5943425”,
“port” : 27017,
“version” : “4.0.3”,
“gitVersion” : “7ea530946fa7880364d88c8d8b6026bbc9ffa48c”
},
“ok” : 1.0
}

Maybe I am missing something but I don’t see a sort stage happening with the following:

db.people.find({ employer: “Underwood LLC” }).sort({ employer: 1})

The winningPlan has a FETCH stage using IXSCAN.

db.people.find({ employer: “Underwood LLC” }).sort({ employer: 1}).explain();

Above query returns a response which doesn’t have something like “stage”: “SORT” which tells us that sort happened in DB using the index. If we have Stage SORT then it tells that sorting happened in RAM and DB was unable to do it in the database using the index.

Even the below query doesn’t have sort stage, because it used the index for sorting.

db.people.find({ “first_name”: “Jessica”, “address.state”: { $lt: “S”} }).sort({ “address.state”: 1 }).explain();

Indeed, I am wrong and you are right. I reread documentation and saw:

Sort Stage

If MongoDB can use an index scan to obtain the requested sort order, the result will not include a SORT stage. Otherwise, if MongoDB cannot use the index to sort, the explain result will include a SORT stage.

I also need clarification from MongoU staff.

This answers the question.

Only the index fields preceding the sort subset must have the equality conditions in the query document.

db.people.find({ “first_name”: “Jessica”, “address.state”: { $lt: “S”} }).sort({ “address.state”: 1 }).explain();

In the above query, the filter condition for “address.state” is not using the equality operator, so the index prefix used as “first_name”: 1, “address.state”: 1

db.people.find({ “first_name”: “Jessica”, “address.state”: “South Dakota” }).sort({ “address.state”: 1 }).explain();

But for the above query, filter condition already used “first_name” & “address.state” as equality operators, the sort is not able to use index to sort.

2 Likes

I opt to not create a new topic for my question because this is still about the lab test.

Can we get the answer from just by looking at the EXPLAIN feature of MongoDB? If so, what part of the information should we be looking into?

Thanks,

Ben

Hey Ben,

My abysmal apologies… I just read your message only now, 11/28/18, too late to reply. What I did is tried the testing of the options (six of them) and I noticed that in EXPLAIN, you would not considered ones with SORT, COLL stuff because they defeat the purpose of efficiency of your query.

Sorry, again!