{"id":163,"date":"2026-07-27T13:12:56","date_gmt":"2026-07-27T20:12:56","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/documentdb\/?p=163"},"modified":"2026-07-29T21:38:35","modified_gmt":"2026-07-30T04:38:35","slug":"query-performance-tuning-guide","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/documentdb\/query-performance-tuning-guide\/","title":{"rendered":"Query Performance Tuning in Azure DocumentDB: A Practical Guide"},"content":{"rendered":"<p id=\"563d\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Writing a query that\u00a0<em class=\"mh\">works<\/em>\u00a0is easy. Writing a query that\u00a0<em class=\"mh\">scales<\/em>\u00a0is a different story.<\/p>\n<p id=\"9a88\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">As collections grow to millions of documents, queries that returned results in milliseconds during development start taking seconds or timing out entirely. The root cause is almost always the same: the database is doing far more work than it needs to.<\/p>\n<p id=\"056c\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">In this post, we\u2019ll take a single, realistic query and walk through the full optimization process: running\u00a0<em class=\"mh\">explain()<\/em>\u00a0to understand what\u2019s happening under the hood, building indexes step by step, and applying the\u00a0<strong class=\"ll fw\">ESR rule<\/strong> to design a compound index that eliminates wasted scans and in-memory sorts. By the end, you\u2019ll have a repeatable mental model for diagnosing and fixing slow queries in Azure DocumentDB.<\/p>\n<h2 id=\"3796\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">Before You Optimize: Find the Slow Queries<\/h2>\n<p id=\"a749\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Before diving into\u00a0<em class=\"mh\">explain()<\/em>, you need to know\u00a0<em class=\"mh\">which<\/em>\u00a0queries to investigate. In Azure DocumentDB, slow query detection is done through\u00a0<strong class=\"ll fw\">Diagnostic Logs<\/strong>\u00a0integrated with\u00a0<strong class=\"ll fw\">Azure Log Analytics<\/strong>.<\/p>\n<p id=\"56f6\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">To get started, enable diagnostic logs for your cluster and route them to a Log Analytics workspace. Full setup instructions are available in the\u00a0<a class=\"by mi\" href=\"https:\/\/learn.microsoft.com\/azure\/documentdb\/how-to-monitor-diagnostics-logs\" target=\"_blank\" rel=\"noopener ugc nofollow\">official documentation<\/a>.<\/p>\n<p id=\"6204\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Once logs are flowing, use the following KQL query in Log Analytics to surface the slowest queries:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">VCoreMongoRequests\r\n| where DurationMs &gt; 1000\r\n| project TimeGenerated, DatabaseName, CollectionName,\r\n          OperationName, DurationMs, PiiCommandText\r\n| order by DurationMs desc\r\n| take 20<\/code><\/pre>\n<p id=\"95f5\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Key fields to look at in the results:<\/p>\n<ul>\n<li id=\"e0d8\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">DurationMs: <\/strong>Query execution time in milliseconds<\/li>\n<li id=\"c4fa\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">OperationName: <\/strong>Type of operation (find, aggregate, update, etc.)<\/li>\n<li id=\"a359\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">CollectionName: <\/strong>Which collection the query ran against<\/li>\n<li id=\"4e90\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">PiiCommandText: <\/strong>The actual command that was executed<\/li>\n<\/ul>\n<p id=\"8ac1\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Once you have a candidate query, copy it and run\u00a0<em class=\"mh\">explain()<\/em>\u00a0on it directly against the database.<\/p>\n<h2>The Problem Query<\/h2>\n<p id=\"3e59\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Let\u2019s start with something you\u2019ve probably written before:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">db.orders.find({\r\n  status: \"shipped\",\r\n  customerId: \"C-4821\",\r\n  createdAt: { $gte: ISODate(\"2024-01-01\") }\r\n}).sort({ createdAt: -1 })<\/code><\/pre>\n<p id=\"379f\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Looks innocent. But under load, this query can become your worst enemy. Let\u2019s diagnose it.<\/p>\n<h3 id=\"4e82\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">Step 1: Run explain() and Read It<\/strong><\/h3>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">db.orders.find({\r\n  status: \"shipped\",\r\n  customerId: \"C-4821\",\r\n  createdAt: { $gte: ISODate(\"2024-01-01\") }\r\n}).sort({ createdAt: -1 }).explain(\"executionStats\")<\/code><\/pre>\n<p class=\"mj mk ml mm mn mo mp mq ff mr co cw\">The explain output:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">{\r\n  \"explainVersion\": 2,\r\n  \"command\": \"db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})\",\r\n  \"explainCommandPlanningTimeMillis\": 0.644,\r\n  \"explainCommandExecTimeMillis\": 81.374,\r\n  \"queryPlanner\": {\r\n    \"namespace\": \"db.orders\",\r\n    \"winningPlan\": {\r\n      \"stage\": \"PARALLEL_SORT_MERGE\",\r\n      \"startupCost\": 36640.95,\r\n      \"totalCost\": 40692.13,\r\n      \"workersPlanned\": 2,\r\n      \"estimatedTotalKeysExamined\": 34722,\r\n      \"inputStage\": {\r\n        \"stage\": \"SORT\",\r\n        \"startupCost\": 35640.93,\r\n        \"totalCost\": 35684.33,\r\n        \"sortKeysCount\": 1,\r\n        \"sortKey\": [\r\n          {\r\n            \"createdAt\": -1\r\n          }\r\n        ],\r\n        \"estimatedTotalKeysExamined\": 17361,\r\n        \"inputStage\": {\r\n          \"stage\": \"COLLSCAN\",\r\n          \"startupCost\": 0,\r\n          \"totalCost\": 34418.4,\r\n          \"runtimeFilterSet\": [\r\n            {\r\n              \"status\": {\r\n                \"$eq\": \"shipped\"\r\n              }\r\n            },\r\n            {\r\n              \"customerId\": {\r\n                \"$eq\": \"C-4821\"\r\n              }\r\n            },\r\n            {\r\n              \"createdAt\": {\r\n                \"$gte\": {\r\n                  \"$date\": \"2024-01-01T00:00:00Z\"\r\n                }\r\n              }\r\n            }\r\n          ],\r\n          \"estimatedTotalKeysExamined\": 17361\r\n        }\r\n      }\r\n    }\r\n  },\r\n  \"executionStats\": {\r\n    \"nReturned\": 18,\r\n    \"executionTimeMillis\": 81.312,\r\n    \"executionStartAtTimeMillis\": 79.32900000000001,\r\n    \"totalDocsExamined\": 18,\r\n    \"totalKeysExamined\": 18,\r\n    \"executionStages\": {\r\n      \"stage\": \"PARALLEL_SORT_MERGE\",\r\n      \"nReturned\": 18,\r\n      \"executionTimeMillis\": 81.312,\r\n      \"executionStartAtTimeMillis\": 79.32900000000001,\r\n      \"totalDocsExamined\": 18,\r\n      \"totalKeysExamined\": 18,\r\n      \"numBlocksFromCache\": 25076,\r\n      \"parallelWorkers\": 2,\r\n      \"inputStage\": {\r\n        \"stage\": \"SORT\",\r\n        \"nReturned\": 6,\r\n        \"executionTimeMillis\": 71.656,\r\n        \"executionStartAtTimeMillis\": 71.655,\r\n        \"totalDocsExamined\": 6,\r\n        \"totalKeysExamined\": 6,\r\n        \"sortMethod\": \"quicksort\",\r\n        \"totalDataSizeSortedBytesEstimate\": 25,\r\n        \"numBlocksFromCache\": 25076,\r\n        \"inputStage\": {\r\n          \"stage\": \"COLLSCAN\",\r\n          \"nReturned\": 6,\r\n          \"executionTimeMillis\": 71.595,\r\n          \"executionStartAtTimeMillis\": 10.154,\r\n          \"totalDocsExamined\": 333333,\r\n          \"totalKeysExamined\": 6,\r\n          \"totalDocsRemovedByRuntimeFilter\": 333327,\r\n          \"numBlocksFromCache\": 25000\r\n        }\r\n      }\r\n    }\r\n  },\r\n  \"ok\": 1\r\n}<\/code><\/pre>\n<p id=\"4822\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Considering the\u00a0<em class=\"mh\">executionTimeMillis\u00a0<\/em>alone, the query looks good and returns the results in a few milliseconds, but let\u2019s look at some key fields in the output:<\/p>\n<ul class=\"\">\n<li id=\"bf2c\" class=\"lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg my mz na cw\" data-selectable-paragraph=\"\"><strong class=\"ll fw\">stage: \u201cCOLLSCAN\u201d:\u00a0<\/strong>Full collection scan, no index used<\/li>\n<li id=\"806a\" class=\"lj lk fv ll b lm nb lo lp lq nc ls lt lu nd lw lx ly ne ma mb mc nf me mf mg my mz na cw\" data-selectable-paragraph=\"\"><strong class=\"ll fw\">totalDocsExamined:\u00a0<\/strong>How many docs were scanned<\/li>\n<li id=\"95b8\" class=\"lj lk fv ll b lm nb lo lp lq nc ls lt lu nd lw lx ly ne ma mb mc nf me mf mg my mz na cw\" data-selectable-paragraph=\"\"><strong class=\"ll fw\">totalKeysExamined:\u00a0<\/strong>How many index keys were examined<\/li>\n<li id=\"ffd8\" class=\"lj lk fv ll b lm nb lo lp lq nc ls lt lu nd lw lx ly ne ma mb mc nf me mf mg my mz na cw\" data-selectable-paragraph=\"\"><strong class=\"ll fw\">nReturned:\u00a0<\/strong>How many docs matched<\/li>\n<li id=\"2f0b\" class=\"lj lk fv ll b lm nb lo lp lq nc ls lt lu nd lw lx ly ne ma mb mc nf me mf mg my mz na cw\" data-selectable-paragraph=\"\"><strong class=\"ll fw\">executionTimeMillis:\u00a0<\/strong>Total execution time<\/li>\n<\/ul>\n<h4>Red flag output:<\/h4>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">\"stage\": \"COLLSCAN\",\r\n\"totalDocsExamined\": 333333,\r\n\"nReturned\": 18,\r\n\"executionTimeMillis\": 81.312<\/code><\/pre>\n<p id=\"391f\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Scanned\u00a0<strong class=\"ll fw\">333K documents<\/strong>\u00a0to return\u00a0<strong class=\"ll fw\">18\u00a0<\/strong>(top stage). That\u2019s a 99.99% waste.<\/p>\n<h3>Step 2: Add a Naive Index (and Why It\u2019s Not Enough)<\/h3>\n<p id=\"8e5e\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Your first instinct might be:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">db.orders.createIndex({ status: 1 })<\/code><\/pre>\n<p id=\"893d\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Run\u00a0<em class=\"mh\">explain()<\/em>\u00a0again:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">\"inputStage\": {\r\n  \"stage\": \"IXSCAN\",\r\n  \"nReturned\": 249800,\r\n  \"executionTimeMillis\": 22.078,\r\n  \"executionStartAtTimeMillis\": 22.077,\r\n  \"indexName\": \"status_1\",\r\n  \"indexUsage\": {\r\n    \"scanLoops\": 249800,\r\n    \"scanType\": \"regular\",\r\n    \"scanKeys\": [\r\n      \"key 1: [(isInequality: false, estimatedEntryCount: 250172)]\"\r\n    ]\r\n  },\r\n  \"totalKeysExamined\": 249800,\r\n  \"numBlocksFromCache\": 70\r\n}<\/code><\/pre>\n<p id=\"1e2e\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Better \u2014 now it\u2019s an index scan (\u2018IXSCAN\u2019). But we\u2019re still scanning\u00a0<strong class=\"ll fw\">249K index keys<\/strong>\u00a0to return 18 results. The index on \u2018status\u2019 narrowed the field, but it didn\u2019t help with \u2018customerId\u2019 or \u2018createdAt\u2019.<\/p>\n<p id=\"63dc\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">The sort still happens\u00a0<strong class=\"ll fw\">in memory<\/strong>\u00a0(\u2018SORT\u2019 stage after \u2018IXSCAN\u2019), which can be expensive.<\/p>\n<h3>Step 3: Apply the ESR Rule<\/h3>\n<p id=\"c99d\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">The\u00a0<strong class=\"ll fw\">ESR rule<\/strong>\u00a0is the golden standard for designing compound indexes:<\/p>\n<p id=\"4f93\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">&gt;\u00a0<strong class=\"ll fw\">E<\/strong>quality \u2192\u00a0<strong class=\"ll fw\">S<\/strong>ort \u2192\u00a0<strong class=\"ll fw\">R<\/strong>ange<\/p>\n<p id=\"6961\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Fields used in\u00a0<strong class=\"ll fw\">equality filters<\/strong>\u00a0come first, followed by fields used in\u00a0<strong class=\"ll fw\">sort<\/strong>, and finally fields used in\u00a0<strong class=\"ll fw\">range filters<\/strong>.<\/p>\n<p id=\"d840\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">For our query:<\/p>\n<ul>\n<li id=\"e298\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">Equality<\/strong>: \u2018status\u2019 (\u201cshipped\u201d) and \u2018customerId\u2019 (\u201cC-4821\u201d) \u2014 exact matches<\/li>\n<li id=\"304e\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">Sort<\/strong>: \u2018createdAt\u2019 \u2014 the sort direction<\/li>\n<li id=\"0a40\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\"><strong class=\"ll fw\">Range<\/strong>: \u2018createdAt\u2019 \u2014 the \u2018$gte\u2019 filter<\/li>\n<\/ul>\n<p id=\"0686\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Since \u2018createdAt\u2019 is used in both sort and range, it appears once, in the sort position (which also satisfies the range scan naturally):<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })<\/code><\/pre>\n<h4>Why this order?<\/h4>\n<ol>\n<li>\u2018customerId\u2019 comes first because it is the most\u00a0<strong class=\"ll fw\">selective<\/strong>\u00a0equality field \u2014 it narrows the result set the most aggressively, so the index skips the vast majority of entries immediately<\/li>\n<li>\u2018status\u2019 follows as a secondary equality filter, further narrowing the remaining entries<\/li>\n<li>\u2018createdAt: -1\u2019 matches the sort direction, so the engine can\u00a0<strong class=\"ll fw\">walk the index in order<\/strong>\u00a0instead of sorting in memory<\/li>\n<li>The range filter on \u2018createdAt\u2019 is applied last, on an already-narrow result set<\/li>\n<\/ol>\n<p><div class=\"alert alert-success\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Lightbulb\"><\/i><strong>Tip:<\/strong><\/p> Within the equality group, always place the most selective field first. A field like \u2018customerId\u2019 (high cardinality \u2014 thousands of distinct values) will skip far more entries than \u2018status\u2019 (low cardinality \u2014 only a handful of values like \u201cshipped\u201d, \u201cpending\u201d, \u201ccancelled\u201d). Note that \u2018customerId\u2019 is not unique here since a customer can have many orders, but it is still far more selective than \u2018status\u2019.<\/div><\/p>\n<p id=\"fd31\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Run\u00a0<em class=\"mh\">explain()<\/em>\u00a0one more time:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">{\r\n  \"explainVersion\": 2,\r\n  \"command\": \"db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})\",\r\n  \"explainCommandPlanningTimeMillis\": 29.028000000000002,\r\n  \"explainCommandExecTimeMillis\": 0.10200000000000001,\r\n  \"queryPlanner\": {\r\n    \"namespace\": \"db.orders\",\r\n    \"winningPlan\": {\r\n      \"stage\": \"FETCH\",\r\n      \"ns\": \"db.orders\",\r\n      \"startupCost\": 0,\r\n      \"totalCost\": 2.01,\r\n      \"estimatedTotalKeysExamined\": 41667,\r\n      \"inputStage\": {\r\n        \"stage\": \"IXSCAN\",\r\n        \"ns\": \"db.orders\",\r\n        \"indexName\": \"customerId_1_status_1_createdAt_-1\",\r\n        \"direction\": \"Forward\",\r\n        \"indexUsage\": {\r\n          \"indexKeyString\": \"{\\\"customerId\\\": 1,\\\"status\\\": 1,\\\"createdAt\\\": -1}\",\r\n          \"isMultiKey\": false,\r\n          \"bounds\": [\r\n            \"[\\\"customerId\\\": [\\\"C-4821\\\", \\\"C-4821\\\"], \\\"status\\\": [\\\"shipped\\\", \\\"shipped\\\"], \\\"createdAt\\\": DESC[{ \\\"$date\\\" : \\\"2024-01-01T00:00:00Z\\\" }, { \\\"$date\\\" : \\\"292278994-08-17T07:12:55.807Z\\\" }]]\"\r\n          ]\r\n        },\r\n        \"startupCost\": 0,\r\n        \"totalCost\": 2.01,\r\n        \"hasOrderBy\": true,\r\n        \"indexFilterSet\": [\r\n          {\r\n            \"status\": {\r\n              \"$eq\": \"shipped\"\r\n            }\r\n          },\r\n          {\r\n            \"customerId\": {\r\n              \"$eq\": \"C-4821\"\r\n            }\r\n          },\r\n          {\r\n            \"createdAt\": {\r\n              \"$gte\": {\r\n                \"$date\": \"2024-01-01T00:00:00Z\"\r\n              }\r\n            }\r\n          }\r\n        ],\r\n        \"estimatedTotalKeysExamined\": 41667\r\n      }\r\n    },\r\n    \"indexCosts\": [\r\n      {\r\n        \"namespace\": \"db.orders\",\r\n        \"costs\": [\r\n          {\r\n            \"indexName\": \"_id_\",\r\n            \"startupCost\": 0.425,\r\n            \"totalCost\": 19644.425,\r\n            \"selectivity\": 1,\r\n            \"correlation\": 0.75,\r\n            \"estimatedPercentIndexPagesLoaded\": 100,\r\n            \"estimatedTotalIndexEntries\": 1000000,\r\n            \"boundarySelectivity\": 1\r\n          }\r\n        ]\r\n      }\r\n    ]\r\n  },\r\n  \"executionStats\": {\r\n    \"nReturned\": 18,\r\n    \"executionTimeMillis\": 0.058,\r\n    \"executionStartAtTimeMillis\": 0.036000000000000004,\r\n    \"totalDocsExamined\": 18,\r\n    \"totalKeysExamined\": 18,\r\n    \"executionStages\": {\r\n      \"stage\": \"FETCH\",\r\n      \"nReturned\": 18,\r\n      \"executionTimeMillis\": 0.058,\r\n      \"executionStartAtTimeMillis\": 0.036000000000000004,\r\n      \"totalKeysExamined\": 18,\r\n      \"numBlocksFromCache\": 25,\r\n      \"inputStage\": {\r\n        \"stage\": \"IXSCAN\",\r\n        \"nReturned\": 18,\r\n        \"executionTimeMillis\": 0.058,\r\n        \"executionStartAtTimeMillis\": 0.036000000000000004,\r\n        \"indexName\": \"customerId_1_status_1_createdAt_-1\",\r\n        \"indexUsage\": {\r\n          \"scanLoops\": 19,\r\n          \"scanType\": \"ordered\",\r\n          \"scanKeys\": [\r\n            \"key 1: [(isInequality: true, estimatedEntryCount: 18)]\"\r\n          ]\r\n        },\r\n        \"totalKeysExamined\": 18,\r\n        \"numBlocksFromCache\": 25\r\n      }\r\n    }\r\n  },\r\n  \"ok\": 1\r\n}<\/code><\/pre>\n<p class=\"mj mk ml mm mn mo mp mq ff mr co cw\"><strong class=\"ll fw\">18 keys examined, 18 returned, 0.058ms, zero in-memory sort.<\/strong>\u00a0That\u2019s what an efficient query looks like.<\/p>\n<h3>Step 4:<strong class=\"ll fw\">\u00a0Confirm the Sort is Index-Backed<\/strong><\/h3>\n<p id=\"e8bf\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Look for the absence of a \u2018SORT\u2019 stage in the winning plan. A plan like this is ideal:<\/p>\n<p id=\"57dd\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">FETCH<\/p>\n<p id=\"c868\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">\u2514\u2500\u2500 IXSCAN { customerId: 1, status: 1, createdAt: -1 }<\/p>\n<p id=\"a0a0\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">If you see this instead, the sort is still in memory:<\/p>\n<p id=\"aa2a\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">SORT<\/p>\n<p id=\"402e\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">\u2514\u2500\u2500 FETCH<\/p>\n<p id=\"5f1e\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">\u2514\u2500\u2500 IXSCAN { status: 1 }<\/p>\n<p id=\"73e4\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">The ESR-ordered compound index eliminates the in-memory sort because the index already delivers documents in the right order.<\/p>\n<h3>Step 5:<strong class=\"ll fw\">\u00a0Go Further with Covered Queries<\/strong><\/h3>\n<p id=\"72a1\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Even with a perfect \u2018IXSCAN\u2019, the engine still performs a \u2018FETCH\u2019 \u2014 it reads the index to find matching keys, then goes back to the collection to retrieve the full document. For high-throughput queries, eliminating this fetch is the next level of optimization.<\/p>\n<p id=\"99f8\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">A\u00a0<strong class=\"ll fw\">covered query<\/strong>\u00a0is one where the index contains\u00a0<em class=\"mh\">all<\/em>\u00a0the fields the query needs \u2014 filters, sort, and projected output. The engine never touches the documents themselves.<\/p>\n<p id=\"c923\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Add a projection that only returns fields already in the index:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">db.orders.find(\r\n  {\r\n    status: \"shipped\",\r\n    customerId: \"C-4821\",\r\n    createdAt: { $gte: ISODate(\"2024-01-01\") }\r\n  },\r\n  { _id: 0, customerId: 1, status: 1, createdAt: 1 }\r\n).sort({ createdAt: -1 }).explain(\"executionStats\")<\/code><\/pre>\n<p data-selectable-paragraph=\"\">The winning plan now looks like:<\/p>\n<p id=\"f1c3\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">IXSCAN { customerId: 1, status: 1, createdAt: -1 }<\/p>\n<p id=\"de0f\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">No \u2018FETCH\u2019 stage at all. The query is fully served by the index.<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">\"stage\": \"IXSCAN\",\r\n\"totalKeysExamined\": 18,\r\n\"nReturned\": 18,\r\n\"executionTimeMillis\": 0.053<\/code><\/pre>\n<p data-selectable-paragraph=\"\"><div class=\"alert alert-primary\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>When is this worth it?<\/strong><\/p>Covered queries shine on high-read collections where the same query runs thousands of times per second and the projected fields are a small subset of a wide document. If you need the full document anyway, the FETCH is unavoidable.<\/div><\/p>\n<h2>Common Mistakes to Avoid<\/h2>\n<h3 class=\"pw-post-body-paragraph lj lk fv ll b lm ny lo lp lq nz ls lt lu oa lw lx ly ob ma mb mc oc me mf mg fo cw\">Putting range fields first<\/h3>\n<p id=\"c55f\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">\u274c Wrong \u2014 range before equality<\/p>\n<p id=\"2e33\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\"><em class=\"mh\">db.orders.createIndex({ createdAt: -1, customerId: 1, status: 1 })<\/em><\/p>\n<p id=\"83a4\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">This forces the engine to scan a wide range of dates before applying the equality filters.<\/p>\n<h3>Mismatching directions <strong class=\"ll fw\">in a multi-field sort<\/strong><\/h3>\n<p id=\"edab\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">To ensure maximum index utilization, the sort specification must match both the\u00a0<strong class=\"ll fw\">fields and their directions<\/strong>\u00a0as declared in the index \u2014\u00a0<strong class=\"ll fw\">or<\/strong>\u00a0be the\u00a0<strong class=\"ll fw\">complete reverse<\/strong>\u00a0of the index.<\/p>\n<p id=\"2e7c\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Given the index { a: 1, b: -1 }:<\/p>\n<ul>\n<li id=\"8ca6\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">.sort({ a: 1, b: -1 }) \u2192 Full index-based sort (forward scan)<\/li>\n<li id=\"4b0b\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">.sort({ a: -1, b: 1 }) \u2192 Full index-based sort (reverse scan)<\/li>\n<li id=\"bdf0\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">.sort({ a: 1, b: 1 }) \u2192 Partial \u2014 only \u2018a\u2019 uses the index; \u2018b\u2019 sorted in memory<\/li>\n<li id=\"a985\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">.sort({ a: 1 }) \u2192 Partial \u2014 only \u2018a\u2019 uses the index<\/li>\n<\/ul>\n<p><div class=\"alert alert-success\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Lightbulb\"><\/i><strong>Tip:<\/strong><\/p>Sorts that don\u2019t fully match the index order still benefit from partial (incremental) ordering \u2014 the index handles what it can, and the database completes the remaining fields in memory.<\/div><\/p>\n<h3>Over-indexing<\/h3>\n<p>Every index costs write performance and storage. Create indexes for your actual query patterns, not preemptively.<\/p>\n<h2 id=\"5828\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">Quick Reference: ESR Checklist<\/h2>\n<p id=\"91f1\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\" data-selectable-paragraph=\"\">Before creating a compound index, answer these three questions:<\/p>\n<ol>\n<li id=\"3964\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">Which fields use <strong class=\"ll fw\">exact match<\/strong>\u00a0($eq)? \u2192 Put them\u00a0<strong class=\"ll fw\">first<\/strong><\/li>\n<li id=\"2eae\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">Which field is used in <strong class=\"ll fw\">sort()<\/strong>? \u2192 Put it\u00a0<strong class=\"ll fw\">in the middle<\/strong><\/li>\n<li id=\"c49f\" class=\"pw-post-body-paragraph lj lk fv ll b lm ln lo lp lq lr ls lt lu lv lw lx ly lz ma mb mc md me mf mg fo cw\">Which fields use <strong class=\"ll fw\">range operators<\/strong>\u00a0($gt, $lt, $gte, $lte, $in)? \u2192 Put them\u00a0<strong class=\"ll fw\">last<\/strong><\/li>\n<\/ol>\n<hr \/>\n<h3>Summary<\/h3>\n<ul>\n<li id=\"0d36\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\"><strong class=\"vx lg\">Diagnostic Logs + Log Analytics<\/strong>\u00a0\u2014 Identifies slow queries in production before they become incidents<\/li>\n<li id=\"f2b2\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\"><strong class=\"vx lg\">Replace COLLSCAN with IXSCAN<\/strong>\u00a0\u2014 Eliminates full collection scan<\/li>\n<li id=\"405e\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\"><strong class=\"vx lg\">Apply ESR rule to compound index<\/strong>\u00a0\u2014 Narrows key scans to near-exact matches<\/li>\n<li id=\"94e6\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\"><strong class=\"vx lg\">Match index direction to sort<\/strong>\u00a0\u2014 Eliminates expensive in-memory sort<\/li>\n<li id=\"8c97\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\"><strong class=\"vx lg\">Use covered queries (projection)<\/strong>\u00a0\u2014 Eliminates the FETCH stage \u2014 index serves the full query<\/li>\n<\/ul>\n<p id=\"9f4c\" class=\"pw-post-body-paragraph vv vw rf vx b vy vz wa wb wc wd we wf wg wh wi wj wk wl wm wn wo wp wq wr ws ip cw\" data-selectable-paragraph=\"\">A single well-designed index transformed an 81.3ms query into a 0.053ms one without application code changes required. With fewer resources consumed per query, your application gains more headroom to scale and can handle higher request rates without adding infrastructure.<\/p>\n<h3>Frequently asked questions<\/h3>\n<h4>How can I find slow queries in Azure DocumentDB?<\/h4>\n<p>Use Diagnostic Logs routed to an Azure Log Analytics workspace. Query `VCoreMongoRequests` for long-running operations, then run `explain()` on a candidate query.<\/p>\n<p>Learn how to configure Diagnostic Logs for your cluster <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/documentdb\/how-to-monitor-diagnostics-logs\">here<\/a>.<\/p>\n<h4>What should I check in `explain(&#8220;executionStats&#8221;)`?<\/h4>\n<p>Review `stage`, `totalDocsExamined`, `totalKeysExamined`, `nReturned`, and `executionTimeMillis`. `COLLSCAN` indicates a full collection scan, while `IXSCAN` means an index was used.<\/p>\n<p>To dive deeper into explain analysis and query performance diagnostics, see the Query Explain guide <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/documentdb\/how-to-read-explain-output\">here<\/a>.<\/p>\n<h4>Why might a single-field index still be inefficient?<\/h4>\n<p>A single-field index may support one filter while leaving other filters and the sort unsupported. This can result in excessive index scans and an in-memory `SORT` stage.<\/p>\n<h4>What is the ESR rule for compound indexes?<\/h4>\n<p>ESR stands for Equality, Sort, Range. Place exact-match fields first, followed by the sort field and then range fields.<\/p>\n<h4>How can I tell whether a sort is index-backed?<\/h4>\n<p>Check that the winning execution plan does not contain a `SORT` stage. For multi-field sorts, the fields and directions should match the index or its complete reverse.<\/p>\n<h4>What is a covered query?<\/h4>\n<p>A covered query has all filter, sort, and projected output fields in the index. You can confirm coverage when `explain()` shows no `FETCH` stage.<\/p>\n<h4>Should I create indexes for every possible query?<\/h4>\n<p>No. Each index adds storage and write-performance costs. Create indexes for real query patterns, then use `explain()` to confirm that they reduce scans and unnecessary sorting.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Writing a query that\u00a0works\u00a0is easy. Writing a query that\u00a0scales\u00a0is a different story. As collections grow to millions of documents, queries that returned results in milliseconds during development start taking seconds or timing out entirely. The root cause is almost always the same: the database is doing far more work than it needs to. In this [&hellip;]<\/p>\n","protected":false},"author":175112,"featured_media":174,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-163","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-document-db"],"acf":[],"blog_post_summary":"<p>Writing a query that\u00a0works\u00a0is easy. Writing a query that\u00a0scales\u00a0is a different story. As collections grow to millions of documents, queries that returned results in milliseconds during development start taking seconds or timing out entirely. The root cause is almost always the same: the database is doing far more work than it needs to. In this [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/posts\/163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/users\/175112"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/comments?post=163"}],"version-history":[{"count":2,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/posts\/163\/revisions"}],"predecessor-version":[{"id":178,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/posts\/163\/revisions\/178"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/media\/174"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/media?parent=163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/categories?post=163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/documentdb\/wp-json\/wp\/v2\/tags?post=163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}