MongoDB Query Performance: Understanding the Query Planner & Advanced Index Types
There are stages to every engineer’s relationship with a database. They might go like this:
- shock: “what is this new thing? This is bad! I’m not used to this!”
- euphoria: “I CAN DO ALL THE THINGS”
- self-doubt: “but I thought I could do all the things?”
- the hero emerges: “I just need to tune this and that and use the right tool for the my use-case”
Working with MongoDB isn’t an exception. MongoDB is from the “NoSQL” family of database, broadly-speaking, and might take some getting used to if you’re used to Postgres, MySQL, or similar RDBMS’s. You’re probably starting into the “self doubt” stage if you’ve recently started working with MongoDB in production and are seeing increased application usage. Things might not be as simple as indexing some obvious fields. We’ve been heavy users of MongoDB for more than a few years at FloQast and have found that, like any production-grade technology, you need to really understand it when serving the needs of many thousands of users. In this post, we’ll look at a few areas that are “beyond the basics” for MongoDB performance. You’ll gain some helpful tools like working with the query planner and knowing when to use specialized indexes.
Query What?
Many mature databases have incredibly complex architectures. They’ll often consist of things like storage engines, query planners and optimizers, replication schemes, sharding approaches, on-disk storage formats, on-wire protocols, etc. Most of these shouldn’t be things that an application developer has to spend a lot of time thinking about, if any. But one of these can be incredibly useful: the query planner.
Most databases with a query language of some kind (SQL for relational databases, the MongoDB query language for MongoDB) have or will eventually have a query planner and query optimizer. These two components work together to take queries from programs and users and make them as efficient and speedy as possible. These are highly-complex areas but pretty self-explanatory:
- query planner: takes an optimized query and determines how to fetch data from the database’s storage engine. Details out which steps will be taken to retrieve data. This might include steps like transforming, fetching, or sorting data
- query optimizer: prior to the planner executing a query, this will look for ways to improve a query. Asks questions like “Can I use an index? Can I combine indexes? Which plan is best?” Cost-based optimizers will often evaluate as many plans as time allots for a given query and then pick the least-costly of these.
Let’s Plan Some Queries
Here’s the sample schema we’ll use as we learn (in case you haven’t already read one of our other posts about MongoDB performance). We are building an application for an imaginary ride-sharing service: FloQars.
{
"_id": "5cf0029caff5056591b0ce7d",
"name": "A. Person",
"email": "[email protected]",
"avatarURL": "https://static.image.com/1234",
"password": "$2a$14$ajq8Q7fbtFRQvXpdCq7Jcuy.Rx1h/L4J60Otx.gyNLbAYctGMJ9tK",
"rides": [
{
"_id": "5cf0029caff5056591b0ce2f",
"date": "01/01/2006",
"driverID": "5cf0029caff5056591b0ce20",
"from": {
"lat": -70.55044,
"lng": 165.39229
},
"to": {
"lat": -29.9244,
"lng": 88.2593
}
},
{
"_id": "5cf0029caff5056591b0ce2a",
"date": "01/02/2006",
"from": {
"lat": -70.55044,
"lng": 165.39229
},
"to": {
"lat": -29.9244,
"lng": 88.2593
}
}
],
"location": {
"current": {
"lat": -70.55044,
"lng": 165.39229
}
}
}
Let’s set up a query against a text field so our fictional users can search through their ride notes. We might try something like this to start: { rideNotes: /receive/ }.
Some experience with databases might tell us that this is going to be a bad idea: we’re not narrowing the query using any other criteria and are using a regular expression. This will likely mean an examination of every document in a collection. We can use the .explain() command to verify our assumptions. The explain command in a query will expose the output of the query planner/optimizer components so we can understand more about our query. Let’s try it!
db.riders.explain().find({ rideNotes: /receive/ })yields:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "floqars.riders",
"indexFilterSet" : false,
"parsedQuery" : {
"rideNotes" : {
"$regex" : "receive"
}
},
"queryHash" : "8A9E186F",
"planCacheKey" : "8A9E186F",
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"rideNotes" : {
"$regex" : "receive"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1588804213, 35),
"signature" : {
"hash" : BinData(0,"+2onXLjgqb4/oXSs1b+80prrEKk="),
"keyId" : NumberLong("6759458550222684161")
}
},
"operationTime" : Timestamp(1588804213, 35)
}
There are some key areas to look at: winningPlan and the stage(s) inside it. This will tell us what the query optimizer deeemed the best way to execute our query and what stages it involves. You should hope to see IXSCAN (index scan) as much as possible here and COLLSCAN (collection scan) as little as possible. The more that MonogDB can utilize your indexes the better. Let’s add an arbitrary sort field to our query and see what other stages might be involved in our query: db.riders.explain().find({ rideNotes: /receive/ }, { _id: -1 } )
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "floqars.riders",
"indexFilterSet" : false,
"parsedQuery" : {
"rideNotes" : {
"$regex" : "receive"
}
},
"queryHash" : "8A9E186F",
"planCacheKey" : "8A9E186F",
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"rideNotes" : {
"$regex" : "receive"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1588804213, 35),
"signature" : {
"hash" : BinData(0,"+2onXLjgqb4/oXSs1b+80prrEKk="),
"keyId" : NumberLong("6759458550222684161")
}
},
"operationTime" : Timestamp(1588804213, 35)
}
Note here that because we added a sort condition on _id an additional stage was added to the query. This reads sort of backwards: Mongo is first scanning everything and applying a regex filter, then returning results sorted by our key. This plan is slightly different than “regular” sorting using another field because _id is an indexed field already.
A few of the other stages include:
PROJECTION: selects certain data attributes and discards the restFETCH: uses IDs produced by a previous stage to fetch documentsLIMIT: examines a limited number of documents and only passes those on to the next stageCOLLSCAN: reads docs one-by-one and passes to the next stageIXSCAN: reads an index and passes results to the next stageSKIP: skips N documents and passes the rest to the next stageSORT_KEY_GENERATOR: prior to a sort stage; generates a key for sortingSORT: sorts documents using a sort keySHARD_MERGEandSHARDING_FILTER: for combinging/filtering documents from queries against sharded clusters
Theory to Practice
It’s great and all that we can look at filters, but you might have noticed something: our query plans aren’t showing the “real world” results of our slow searches. The explain command is executing quickly and just telling us what the planner would do. We should choose a verbosity-mode for explain so we can see more detail about how our queries are actually going to behave. You can do that by passing in a verbosity mode to the explain invocation in your query like so: db.riders.explain('allPlansExecution').find({ rideNotes: /receive/ }, { avatarURL: -1 } ). This yields a lot more detail:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "floqars.riders",
"indexFilterSet": false,
"parsedQuery": {
"rideNotes": {
"$regex": "receive"
}
},
"winningPlan": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"avatarURL": -1
},
"inputStage": {
"stage": "COLLSCAN",
"filter": {
"rideNotes": {
"$regex": "receive"
}
},
"direction": "forward"
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 29848,
"executionTimeMillis": 2906,
"totalKeysExamined": 0,
"totalDocsExamined": 1615003,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 29848,
"executionTimeMillisEstimate": 1419,
"works": 1615005,
"advanced": 29848,
"needTime": 1585156,
"needYield": 0,
"saveState": 12617,
"restoreState": 12617,
"isEOF": 1,
"transformBy": {
"avatarURL": -1
},
"inputStage": {
"stage": "COLLSCAN",
"filter": {
"rideNotes": {
"$regex": "receive"
}
},
"nReturned": 29848,
"executionTimeMillisEstimate": 1400,
"works": 1615005,
"advanced": 29848,
"needTime": 1585156,
"needYield": 0,
"saveState": 12617,
"restoreState": 12617,
"isEOF": 1,
"direction": "forward",
"docsExamined": 1615003
}
},
"allPlansExecution": []
},
"ok": 1,
"$clusterTime": {
"clusterTime": Timestamp(1588806878, 53),
"signature": {
"hash": BinData(0, "rPDyp8XIs+c/T4nZVFPC22kskN0="),
"keyId": NumberLong("6759458550222684161")
}
},
"operationTime": Timestamp(1588806878, 53)
}
Whoa! That’s a lot more detail. One thing that might stand out right away is executionTimeMillis being nearly 3 seconds. That’s a long time for a database query, especially a user-facing one. We want our users saving time, not waiting for things to load and that won’t due. Let’s look at one of the stages in more depth:
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"rideNotes" : {
"$regex" : "receive"
}
},
"nReturned" : 29848,
"executionTimeMillisEstimate" : 1400,
"works" : 1615005,
"advanced" : 29848,
"needTime" : 1585156,
"needYield" : 0,
"saveState" : 12617,
"restoreState" : 12617,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 1615003
}
There are a few things to note here. First, you can see how many documents this stage returned in nReturned. Depending on your query, this may or may not be a number you are happy with. In a perfect world, each stage is returning as few documents as possible. In some cases, though, a user may genuinely be looking for a large amount of data and in those cases you’ll need a pagination strategy or similar.
The next thing to note is possibly the most useful field in the query planner: docsExamined. This is how many documents were examined in a given stage and you want this number to be low. Even more importantly, you want to look at the ratio of docsExamined and nReturned. These numbers together will help you determine “how much work is MongoDB doing to return me useful data?”. We can see here that our “hit ratio” is 29848 / 1615003 or ~1.8%. This is pretty bad – we are operating at absymal levels of efficiency. If your cluster is examining a high number of docs with respect to those that it is returning, you’ll likely see a few things happen:
- longer query times overall
- more utilization of your clusters CPU & memory resources
- choppier cache residency and eviction
- locked & blocking queries under load
These all add up to worse performance for more effort and frustrated users. The good news is that you can catch these cases either in pre-production when testing out queries or by turning on the MongoDB profiler so that slow queries are logged and create notifications.
Special Index Types
MongoDB’s index types are generally sufficient for most application needs. You can use single-field, compound, and multikey indexes for most application use-cases. If you need a referesher on these, check out one of our other posts about MonogDB performance. But sometimes you need something more specialized. Our text-search query above is probably a good case; regex searches are going to be pretty slow almost no matter what we do based on their nature. We could add another constraint to our query to just query for a certain user’s data or maybe improve our indexing strategy. But what could we do if this wasn’t possible? Mongo has an answer: we can probably use Mongo’s full-text search index type. I say “probably” because full-text search is a domain unto itself and there are systems like ElasticSearch that you might need to consider for extremely sophisticated search. Mongo’s text index type should at least get us further than our regex is currently doing.
Let’s add the full-text index to the rideNotes field:
db.riders.createIndex({ rideNotes: "text", description: "my first full-text index"})
A couple things to note about the full-text index type before we try querying with it:
- you can only have one full-text index per collection. However, it can apply to multiple fields
- this index type typically takes up much more space than others because of the data structures involved in full-text search (posting lists, stopwords, etc.). This index takes up ~200 MB vs 16.2MB for the
_idindex in this particular collection. - you must use a certain query syntax to leverage full-text search
- as with any index, there may be minor write performance penalties associated with an additional index
But enough about the tradeoffs – let’s put this thing to the test. This is a good time to warn you that query planner output can be pretty big! Don’t worry though – you can tackle it one stage at a time.
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "floqars.riders",
"indexFilterSet": false,
"parsedQuery": {
"$text": {
"$search": "receive",
"$language": "english",
"$caseSensitive": false,
"$diacriticSensitive": false
}
},
"winningPlan": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"_id": -1
},
"inputStage": {
"stage": "TEXT",
"indexPrefix": {},
"indexName": "rideNotes_text_description_text",
"parsedTextQuery": {
"terms": ["receiv"],
"negatedTerms": [],
"phrases": [],
"negatedPhrases": []
},
"textIndexVersion": 3,
"inputStage": {
"stage": "TEXT_MATCH",
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "OR",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"_fts": "text",
"_ftsx": 1
},
"indexName": "rideNotes_text_description_text",
"isMultiKey": true,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "backward",
"indexBounds": {}
}
}
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 33820,
"executionTimeMillis": 299,
"totalKeysExamined": 33820,
"totalDocsExamined": 33820,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 33820,
"executionTimeMillisEstimate": 222,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"transformBy": {
"_id": -1
},
"inputStage": {
"stage": "TEXT",
"nReturned": 33820,
"executionTimeMillisEstimate": 208,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"indexPrefix": {},
"indexName": "rideNotes_text_description_text",
"parsedTextQuery": {
"terms": ["receiv"],
"negatedTerms": [],
"phrases": [],
"negatedPhrases": []
},
"textIndexVersion": 3,
"inputStage": {
"stage": "TEXT_MATCH",
"nReturned": 33820,
"executionTimeMillisEstimate": 206,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"docsRejected": 0,
"inputStage": {
"stage": "FETCH",
"nReturned": 33820,
"executionTimeMillisEstimate": 206,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"docsExamined": 33820,
"alreadyHasObj": 0,
"inputStage": {
"stage": "OR",
"nReturned": 33820,
"executionTimeMillisEstimate": 31,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"dupsTested": 33820,
"dupsDropped": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 33820,
"executionTimeMillisEstimate": 26,
"works": 33821,
"advanced": 33820,
"needTime": 0,
"needYield": 0,
"saveState": 264,
"restoreState": 264,
"isEOF": 1,
"keyPattern": {
"_fts": "text",
"_ftsx": 1
},
"indexName": "rideNotes_text_description_text",
"isMultiKey": true,
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "backward",
"indexBounds": {},
"keysExamined": 33820,
"seeks": 1,
"dupsTested": 33820,
"dupsDropped": 0
}
}
}
}
}
}
},
"ok": 1,
"$clusterTime": {
"clusterTime": Timestamp(1588808094, 73),
"signature": {
"hash": BinData(0, "Y6IyUuYrIT7kMyn9Oob5OHZ9OWE="),
"keyId": NumberLong("6759458550222684161")
}
},
"operationTime": Timestamp(1588808094, 73)
}
There are a few things to note here, but let’s start with the good news: that was much, much faster! Here are the top-line execution stats we care most about:
"nReturned" : 33820,
"executionTimeMillis" : 299,
"totalKeysExamined" : 33820,
"totalDocsExamined" : 33820,
The query plan was also much more complicated than before and you might have noticed some things we didnt’ cover before like OR or TEXT stages. Take a minute to read through the stages and describe them in a sentence or so to yourself. Try to aim for things like “an index is being scanned and returning IDs”, or “a FETCH stage is retrieving documents from IDs”. You should do this more and more as you work with the query planner and optimizer. Over time, you’ll start to “see” what Mongo is doing and think of ways things could be improved. You might also be surprised by what the optimizer does and that’s OK – try out different combinations to see what gives you the best results. A database’s query optimizer is often, well, optimized over time by the core team and usually gets better and smarter “for free” as you upgrade to newer versions of the database. How cool is that! It’s not often that things get smarter and faster without direct effort from a user.
We can really see how this index is better suited to our user-case: it was roughly 10 times faster (~300ms), returned more results, and we only returned what we examined (remember that important nReturned / docsExamined ratio). Together these all mean this is a much more efficient query and the time it takes really bears that out. What’s more, the user can apply more flexible search queries than the regex would let us before. Huzzah! Don’t forget the tradeoffs though: this faster read comes at a slight write penalty and takes up a good deal of storage.
Wrapping up
We’ve taken a look at a working with the query planner and optimizer as a way to understand and improve our MongoDB queries. We also looked at some specialized index types that MongoDB offers for cases where regular indexes won’t cut it. I hope you feel better armed to tackle slow queries and that working with MongoDB feels a little less of a dark art. Happy querying!
Mark Thomas
Mark is a Staff Software Engineer at FloQast. He enjoys good coffee, a solid gif, and beautiful systems.
Back to Blog