{"id":288,"date":"2017-05-22T06:34:14","date_gmt":"2017-05-22T13:34:14","guid":{"rendered":"http:\/\/www.kenwalger.com\/blog\/?p=288"},"modified":"2017-06-25T10:46:56","modified_gmt":"2017-06-25T17:46:56","slug":"mongodb-explain-explained","status":"publish","type":"post","link":"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/mongodb-explain-explained\/","title":{"rendered":"MongoDB explain() explained"},"content":{"rendered":"<p>There are many different considerations to be made when running queries in <a href=\"https:\/\/www.mongodb.com\">MongoDB<\/a>. A helpful thing to use in the <a href=\"https:\/\/docs.mongodb.com\/getting-started\/shell\/client\/\">mongo shell<\/a> when running a <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/method\/db.collection.find\/\">find()<\/a> operation is to use the <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/method\/cursor.explain\/\">explain()<\/a> method. In this blog post, I&#8217;ll take a look at some of the options for explain() and what the results mean.<\/p>\n<h3>explain()<\/h3>\n<p>As discussed in a previous post on <a href=\"http:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/indexing-in-mongodb\/\">indexing<\/a> in MongoDB, we can use the explain() method to learn about the selected query plan. This allows for an examination of the performance of a given query. It can be used in the following manner:<\/p>\n<pre>db.collection.find().explain()\n<\/pre>\n<p>The information generated can be used to see what index is being used for a query, if the query is a covered query, \u00a0and which servers in a <a href=\"https:\/\/docs.mongodb.com\/manual\/sharding\/\">sharded collection<\/a> the query is run against, to name a few.<\/p>\n<p>Three different verbosity modes can be utilized to determine the amount of information provided.<\/p>\n<h5>Verbosity modes<\/h5>\n<ul>\n<li><strong>queryPlanner<\/strong> &#8211; the given query provided in the find() method is put through the <a href=\"https:\/\/docs.mongodb.com\/manual\/core\/query-plans\/\">query optimizer<\/a> to find the most efficient query. This &#8220;winning plan&#8221; is then passed to the <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/explain-results\/#explain.queryPlanner\">queryPlanner <\/a>and the information is returned for the evaluated query. The query is not run in this mode. As a result things like query time, e.g. <code>executionTimeMillisEstimate<\/code> are true estimates since the query has not been executed.<\/li>\n<li><strong>executionStats<\/strong> &#8211; when running in this mode, the query optimizer is run and the query is fully executed. The information returned details the results of the are what actually happened during that specific query.<\/li>\n<li><strong>allPlansExecution<\/strong> &#8211; as the name might suggest, this mode returns information about all possible query plans. While the winning plan is executed and statistics returned for it, other candidate plan information is returned as well. This is the default mode of explain().<\/li>\n<\/ul>\n<p>The variety of information these different modes provides can be extremely useful. Let&#8217;s take a look at some returned results of explain() and walk through what they show.<\/p>\n<h3>Results<\/h3>\n<p>For this example, I will use a test example database of a blog. The database contains two collections,\u00a0<em>users<\/em> and\u00a0<em>articles<\/em>, and is running on a single, unsharded, machine. Each collection has, roughly, 550,500 documents and is not indexed beyond the index for <code>_id<\/code>.<\/p>\n<p>Let&#8217;s start with looking at what gets returned from a query for a single username. And take a look at some of the bits and pieces of information provided.<\/p>\n<pre>db.users.find( { \"username\": \"User_9\"} ).explain()\n<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" data-attachment-id=\"298\" data-permalink=\"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/mongodb-explain-explained\/attachment\/basic_find_with_notes\/\" data-orig-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?fit=600%2C358&amp;ssl=1\" data-orig-size=\"600,358\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"basic_find_with_notes\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?fit=300%2C179&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?fit=600%2C358&amp;ssl=1\" class=\"alignnone size-full wp-image-298\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?resize=600%2C358\" alt=\"explain output\" width=\"600\" height=\"358\" srcset=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?w=600&amp;ssl=1 600w, https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/basic_find_with_notes-1.png?resize=300%2C179&amp;ssl=1 300w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/a><\/p>\n<p>The parsedQuery section is the query we are exploring. The query\u00a0<em>stage<\/em> provides a description of the type of operation that occurred for the winning plan.<\/p>\n<h3>Operation Types<\/h3>\n<ul>\n<li><strong>COLLSCAN<\/strong> &#8211; indicates a collection scan occurred for the query, meaning that the query looked at\u00a0<strong>each<\/strong> document to get the results<\/li>\n<li><strong>IXSCAN<\/strong> &#8211; indicates an index was used for the query<\/li>\n<li><strong>FETCH<\/strong> &#8211; for retrieving\u00a0documents<\/li>\n<li><strong>SHARD_MERGE<\/strong> &#8211; the result of merging data from shards<\/li>\n<\/ul>\n<p>The stage is a tree structure and can have multiple, child, stages. The\u00a0<em>direction<\/em> of the query shows whether the query was performed in a forward or reverse order. The\u00a0<em>serverInfo<\/em> section displays information on the server the query was run against and includes, in the\u00a0<em>version<\/em> key, the version of the MongoDB database. If the collection was in a sharded environment, each accessed shard would be listed in the\u00a0<em>serverInfo<\/em>.<\/p>\n<p>When the command is run using the &#8220;executionStats&#8221; verbosity mode:<\/p>\n<pre>db.users.find({ \"username\": \"User_9\"} ).explain(\"executionStats\")<\/pre>\n<p>additional information is provided as a result of the query being run on the data.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" data-attachment-id=\"299\" data-permalink=\"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/mongodb-explain-explained\/attachment\/executionstats_with_notes\/\" data-orig-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?fit=600%2C631&amp;ssl=1\" data-orig-size=\"600,631\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"executionStats_with_notes\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?fit=285%2C300&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?fit=600%2C631&amp;ssl=1\" class=\"alignnone size-full wp-image-299\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?resize=600%2C631\" alt=\"explain with executionStats\" width=\"600\" height=\"631\" srcset=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?w=600&amp;ssl=1 600w, https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/executionStats_with_notes.png?resize=285%2C300&amp;ssl=1 285w\" sizes=\"auto, (max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/a><\/p>\n<p>Here we see, among other things, the time the query took to run, along with how many documents were returned, <code>nReturned<\/code>, and how many documents were examined by the database, <code>totalDocsExamined<\/code>. As mentioned in my post on <a href=\"http:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/indexing-in-mongodb\/\">indexing<\/a>, ideally these two numbers should be very close to the same value.<\/p>\n<h3>Wrap Up<\/h3>\n<p>There is a lot of information available when using the explain() method. It provides some great information about how queries are\u00a0<em>actually<strong>\u00a0<\/strong><\/em>being run and gives an indication as to where a collection can benefit from an index. It should be your first stop when examining slow queries before moving onto other <a href=\"http:\/\/www.kenwalger.com\/blog\/nosql\/mongodb-cli-tools\/\">MongoDB tools<\/a>.<\/p>\n<p>There are a lot of MongoDB specific terms in this post. I created a <a href=\"https:\/\/www.echoskillstore.com\/MongoDB-Dictionary\/45103\">MongoDB Dictionary<\/a> skill for the <a href=\"https:\/\/www.amazon.com\/gp\/product\/B01DFKC2SO\/ref=as_li_tl?ie=UTF8&amp;camp=1789&amp;creative=9325&amp;creativeASIN=B01DFKC2SO&amp;linkCode=as2&amp;tag=kenwalgersite-20&amp;linkId=f9e513223de2525a72b95cf9561db55b\" target=\"_blank\" rel=\"noopener noreferrer\">Amazon Echo<\/a>\u00a0line of products. Check it out and you can say &#8220;Alexa, ask MongoDB what is an index?&#8221; and get a helpful response.<\/p>\n<hr \/>\n<p><em>Follow me on Twitter <a href=\"https:\/\/www.twitter.com\/kenwalger\">@kenwalger<\/a> to get the latest updates on my postings.<\/em><\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-48 synved-social-resolution-single synved-social-provider-facebook nolightbox\" data-provider=\"facebook\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288&#038;t=MongoDB%20explain%28%29%20explained&#038;s=100&#038;p&#091;url&#093;=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288&#038;p&#091;images&#093;&#091;0&#093;=https%3A%2F%2Fi0.wp.com%2Fwww.kenwalger.com%2Fblog%2Fwp-content%2Fuploads%2F2017%2F05%2Fexplain.png%3Ffit%3D125%252C125%26ssl%3D1&#038;p&#091;title&#093;=MongoDB%20explain%28%29%20explained\" style=\"font-size: 0px;width:48px;height:48px;margin:0;margin-bottom:5px;margin-right:5px\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"Facebook\" title=\"Share on Facebook\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"48\" height=\"48\" style=\"display: inline;width:48px;height:48px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/96x96\/facebook.png?resize=48%2C48&#038;ssl=1\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-48 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288&#038;text=Hey%20check%20this%20out\" style=\"font-size: 0px;width:48px;height:48px;margin:0;margin-bottom:5px;margin-right:5px\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"48\" height=\"48\" style=\"display: inline;width:48px;height:48px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/96x96\/twitter.png?resize=48%2C48&#038;ssl=1\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-48 synved-social-resolution-single synved-social-provider-reddit nolightbox\" data-provider=\"reddit\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Reddit\" href=\"https:\/\/www.reddit.com\/submit?url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288&#038;title=MongoDB%20explain%28%29%20explained\" style=\"font-size: 0px;width:48px;height:48px;margin:0;margin-bottom:5px;margin-right:5px\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"reddit\" title=\"Share on Reddit\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"48\" height=\"48\" style=\"display: inline;width:48px;height:48px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/96x96\/reddit.png?resize=48%2C48&#038;ssl=1\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-48 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288&#038;title=MongoDB%20explain%28%29%20explained\" style=\"font-size: 0px;width:48px;height:48px;margin:0;margin-bottom:5px;margin-right:5px\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"48\" height=\"48\" style=\"display: inline;width:48px;height:48px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/96x96\/linkedin.png?resize=48%2C48&#038;ssl=1\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-48 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=MongoDB%20explain%28%29%20explained&#038;body=Hey%20check%20this%20out:%20https%3A%2F%2Fwww.kenwalger.com%2Fblog%2Fwp-json%2Fwp%2Fv2%2Fposts%2F288\" style=\"font-size: 0px;width:48px;height:48px;margin:0;margin-bottom:5px\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"48\" height=\"48\" style=\"display: inline;width:48px;height:48px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/96x96\/mail.png?resize=48%2C48&#038;ssl=1\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>There are many different considerations to be made when running queries in MongoDB. A helpful thing to use in the mongo shell when running a find() operation is to use the explain() method. In this blog post, I&#8217;ll take a look at some of the options for explain() and what the results mean. explain() As &hellip; <a href=\"https:\/\/www.kenwalger.com\/blog\/nosql\/mongodb\/mongodb-explain-explained\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;MongoDB explain() explained&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":301,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pmpro_default_level":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,3],"tags":[],"yst_prominent_words":[536,117,104,538,535,531,534,108,389,526,539,87,527,529,537,533,434,532,530,528],"class_list":["post-288","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mongodb","category-nosql","pmpro-has-access"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.kenwalger.com\/blog\/wp-content\/uploads\/2017\/05\/explain.png?fit=125%2C125&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8lx70-4E","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/288","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/comments?post=288"}],"version-history":[{"count":9,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/288\/revisions"}],"predecessor-version":[{"id":382,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/posts\/288\/revisions\/382"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/media\/301"}],"wp:attachment":[{"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/media?parent=288"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/categories?post=288"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/tags?post=288"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/www.kenwalger.com\/blog\/wp-json\/wp\/v2\/yst_prominent_words?post=288"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}