Writing a query that works is easy. Writing a query that scales is 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 post, we’ll take a single, realistic query and walk through the full optimization process: running explain() to understand what’s happening under the hood, building indexes step by step, and applying the ESR rule to design a compound index that eliminates wasted scans and in-memory sorts. By the end, you’ll have a repeatable mental model for diagnosing and fixing slow queries in Azure DocumentDB.Before You Optimize: Find the Slow QueriesBefore diving into explain(), you need to know which queries to investigate. In Azure DocumentDB, slow query detection is done through Diagnostic Logs integrated with Azure Log Analytics.To get started, enable diagnostic logs for your cluster and route them to a Log Analytics workspace. Full setup instructions are available in the official documentation.Once logs are flowing, use the following KQL query in Log Analytics to surface the slowest queries:VCoreMongoRequests| where DurationMs > 1000| project TimeGenerated, DatabaseName, CollectionName, OperationName, DurationMs, PiiCommandText| order by DurationMs desc| take 20Key fields to look at in the results: DurationMs: Query execution time in milliseconds OperationName: Type of operation (find, aggregate, update, etc.) CollectionName: Which collection the query ran against PiiCommandText: The actual command that was executedOnce you have a candidate query, copy it and run explain() on it directly against the database.The Problem QueryLet’s start with something you’ve probably written before:db.orders.find({ status: "shipped", customerId: "C-4821", createdAt: { $gte: ISODate("2024-01-01") }}).sort({ createdAt: -1 })Looks innocent. But under load, this query can become your worst enemy. Let’s diagnose it.Step 1: Run explain() and Read Itdb.orders.find({ status: "shipped", customerId: "C-4821", createdAt: { $gte: ISODate("2024-01-01") }}).sort({ createdAt: -1 }).explain("executionStats")The explain output:{ "explainVersion": 2, "command": "db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})", "explainCommandPlanningTimeMillis": 0.644, "explainCommandExecTimeMillis": 81.374, "queryPlanner": { "namespace": "db.orders", "winningPlan": { "stage": "PARALLEL_SORT_MERGE", "startupCost": 36640.95, "totalCost": 40692.13, "workersPlanned": 2, "estimatedTotalKeysExamined": 34722, "inputStage": { "stage": "SORT", "startupCost": 35640.93, "totalCost": 35684.33, "sortKeysCount": 1, "sortKey": [ { "createdAt": -1 } ], "estimatedTotalKeysExamined": 17361, "inputStage": { "stage": "COLLSCAN", "startupCost": 0, "totalCost": 34418.4, "runtimeFilterSet": [ { "status": { "$eq": "shipped" } }, { "customerId": { "$eq": "C-4821" } }, { "createdAt": { "$gte": { "$date": "2024-01-01T00:00:00Z" } } } ], "estimatedTotalKeysExamined": 17361 } } } }, "executionStats": { "nReturned": 18, "executionTimeMillis": 81.312, "executionStartAtTimeMillis": 79.32900000000001, "totalDocsExamined": 18, "totalKeysExamined": 18, "executionStages": { "stage": "PARALLEL_SORT_MERGE", "nReturned": 18, "executionTimeMillis": 81.312, "executionStartAtTimeMillis": 79.32900000000001, "totalDocsExamined": 18, "totalKeysExamined": 18, "numBlocksFromCache": 25076, "parallelWorkers": 2, "inputStage": { "stage": "SORT", "nReturned": 6, "executionTimeMillis": 71.656, "executionStartAtTimeMillis": 71.655, "totalDocsExamined": 6, "totalKeysExamined": 6, "sortMethod": "quicksort", "totalDataSizeSortedBytesEstimate": 25, "numBlocksFromCache": 25076, "inputStage": { "stage": "COLLSCAN", "nReturned": 6, "executionTimeMillis": 71.595, "executionStartAtTimeMillis": 10.154, "totalDocsExamined": 333333, "totalKeysExamined": 6, "totalDocsRemovedByRuntimeFilter": 333327, "numBlocksFromCache": 25000 } } } }, "ok": 1}Considering the executionTimeMillis alone, the query looks good and returns the results in a few milliseconds, but let’s look at some key fields in the output: stage: “COLLSCAN”: Full collection scan, no index used totalDocsExamined: How many docs were scanned totalKeysExamined: How many index keys were examined nReturned: How many docs matched executionTimeMillis: Total execution timeRed flag output:"stage": "COLLSCAN","totalDocsExamined": 333333,"nReturned": 18,"executionTimeMillis": 81.312Scanned 333K documents to return 18 (top stage). That’s a 99.99% waste.Step 2: Add a Naive Index (and Why It’s Not Enough)Your first instinct might be:db.orders.createIndex({ status: 1 })Run explain() again:"inputStage": { "stage": "IXSCAN", "nReturned": 249800, "executionTimeMillis": 22.078, "executionStartAtTimeMillis": 22.077, "indexName": "status_1", "indexUsage": { "scanLoops": 249800, "scanType": "regular", "scanKeys": [ "key 1: [(isInequality: false, estimatedEntryCount: 250172)]" ] }, "totalKeysExamined": 249800, "numBlocksFromCache": 70}Better — now it’s an index scan (‘IXSCAN’). But we’re still scanning 249K index keys to return 18 results. The index on ‘status’ narrowed the field, but it didn’t help with ‘customerId’ or ‘createdAt’.The sort still happens in memory (‘SORT’ stage after ‘IXSCAN’), which can be expensive.Step 3: Apply the ESR RuleThe ESR rule is the golden standard for designing compound indexes:> Equality → Sort → RangeFields used in equality filters come first, followed by fields used in sort, and finally fields used in range filters.For our query: Equality: ‘status’ (“shipped”) and ‘customerId’ (“C-4821”) — exact matches Sort: ‘createdAt’ — the sort direction Range: ‘createdAt’ — the ‘$gte’ filterSince ‘createdAt’ is used in both sort and range, it appears once, in the sort position (which also satisfies the range scan naturally):db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })Why this order? ‘customerId’ comes first because it is the most selective equality field — it narrows the result set the most aggressively, so the index skips the vast majority of entries immediately ‘status’ follows as a secondary equality filter, further narrowing the remaining entries ‘createdAt: -1’ matches the sort direction, so the engine can walk the index in order instead of sorting in memory The range filter on ‘createdAt’ is applied last, on an already-narrow result set[alert type="tip" heading="Tip:"] Within the equality group, always place the most selective field first. A field like ‘customerId’ (high cardinality — thousands of distinct values) will skip far more entries than ‘status’ (low cardinality — only a handful of values like “shipped”, “pending”, “cancelled”). Note that ‘customerId’ is not unique here since a customer can have many orders, but it is still far more selective than ‘status’.[/alert]Run explain() one more time:{ "explainVersion": 2, "command": "db.runCommand({explain: { 'find' : 'orders', 'filter' : { 'status' : 'shipped', 'customerId' : 'C-4821', 'createdAt' : { '$gte' : ISODate('2024-01-01T00:00:00Z') } }, 'sort' : { 'createdAt' : -1 } }})", "explainCommandPlanningTimeMillis": 29.028000000000002, "explainCommandExecTimeMillis": 0.10200000000000001, "queryPlanner": { "namespace": "db.orders", "winningPlan": { "stage": "FETCH", "ns": "db.orders", "startupCost": 0, "totalCost": 2.01, "estimatedTotalKeysExamined": 41667, "inputStage": { "stage": "IXSCAN", "ns": "db.orders", "indexName": "customerId_1_status_1_createdAt_-1", "direction": "Forward", "indexUsage": { "indexKeyString": "{\"customerId\": 1,\"status\": 1,\"createdAt\": -1}", "isMultiKey": false, "bounds": [ "[\"customerId\": [\"C-4821\", \"C-4821\"], \"status\": [\"shipped\", \"shipped\"], \"createdAt\": DESC[{ \"$date\" : \"2024-01-01T00:00:00Z\" }, { \"$date\" : \"292278994-08-17T07:12:55.807Z\" }]]" ] }, "startupCost": 0, "totalCost": 2.01, "hasOrderBy": true, "indexFilterSet": [ { "status": { "$eq": "shipped" } }, { "customerId": { "$eq": "C-4821" } }, { "createdAt": { "$gte": { "$date": "2024-01-01T00:00:00Z" } } } ], "estimatedTotalKeysExamined": 41667 } }, "indexCosts": [ { "namespace": "db.orders", "costs": [ { "indexName": "_id_", "startupCost": 0.425, "totalCost": 19644.425, "selectivity": 1, "correlation": 0.75, "estimatedPercentIndexPagesLoaded": 100, "estimatedTotalIndexEntries": 1000000, "boundarySelectivity": 1 } ] } ] }, "executionStats": { "nReturned": 18, "executionTimeMillis": 0.058, "executionStartAtTimeMillis": 0.036000000000000004, "totalDocsExamined": 18, "totalKeysExamined": 18, "executionStages": { "stage": "FETCH", "nReturned": 18, "executionTimeMillis": 0.058, "executionStartAtTimeMillis": 0.036000000000000004, "totalKeysExamined": 18, "numBlocksFromCache": 25, "inputStage": { "stage": "IXSCAN", "nReturned": 18, "executionTimeMillis": 0.058, "executionStartAtTimeMillis": 0.036000000000000004, "indexName": "customerId_1_status_1_createdAt_-1", "indexUsage": { "scanLoops": 19, "scanType": "ordered", "scanKeys": [ "key 1: [(isInequality: true, estimatedEntryCount: 18)]" ] }, "totalKeysExamined": 18, "numBlocksFromCache": 25 } } }, "ok": 1}18 keys examined, 18 returned, 0.058ms, zero in-memory sort. That’s what an efficient query looks like.Step 4: Confirm the Sort is Index-BackedLook for the absence of a ‘SORT’ stage in the winning plan. A plan like this is ideal:FETCH└── IXSCAN { customerId: 1, status: 1, createdAt: -1 }If you see this instead, the sort is still in memory:SORT└── FETCH└── IXSCAN { status: 1 }The ESR-ordered compound index eliminates the in-memory sort because the index already delivers documents in the right order.Step 5: Go Further with Covered QueriesEven with a perfect ‘IXSCAN’, the engine still performs a ‘FETCH’ — 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.A covered query is one where the index contains all the fields the query needs — filters, sort, and projected output. The engine never touches the documents themselves.Add a projection that only returns fields already in the index:db.orders.find( { status: "shipped", customerId: "C-4821", createdAt: { $gte: ISODate("2024-01-01") } }, { _id: 0, customerId: 1, status: 1, createdAt: 1 }).sort({ createdAt: -1 }).explain("executionStats")The winning plan now looks like:IXSCAN { customerId: 1, status: 1, createdAt: -1 }No ‘FETCH’ stage at all. The query is fully served by the index."stage": "IXSCAN","totalKeysExamined": 18,"nReturned": 18,"executionTimeMillis": 0.053[alert type="note" heading="When is this worth it?"]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.[/alert]Common Mistakes to AvoidPutting range fields first❌ Wrong — range before equalitydb.orders.createIndex({ createdAt: -1, customerId: 1, status: 1 })This forces the engine to scan a wide range of dates before applying the equality filters.Mismatching directions in a multi-field sortTo ensure maximum index utilization, the sort specification must match both the fields and their directions as declared in the index — or be the complete reverse of the index.Given the index { a: 1, b: -1 }: .sort({ a: 1, b: -1 }) → Full index-based sort (forward scan) .sort({ a: -1, b: 1 }) → Full index-based sort (reverse scan) .sort({ a: 1, b: 1 }) → Partial — only ‘a’ uses the index; ‘b’ sorted in memory .sort({ a: 1 }) → Partial — only ‘a’ uses the index[alert type="tip" heading="Tip:"]Sorts that don’t fully match the index order still benefit from partial (incremental) ordering — the index handles what it can, and the database completes the remaining fields in memory.[/alert]Over-indexingEvery index costs write performance and storage. Create indexes for your actual query patterns, not preemptively.Quick Reference: ESR ChecklistBefore creating a compound index, answer these three questions: Which fields use exact match ($eq)? → Put them first Which field is used in sort()? → Put it in the middle Which fields use range operators ($gt, $lt, $gte, $lte, $in)? → Put them lastSummary Diagnostic Logs + Log Analytics — Identifies slow queries in production before they become incidents Replace COLLSCAN with IXSCAN — Eliminates full collection scan Apply ESR rule to compound index — Narrows key scans to near-exact matches Match index direction to sort — Eliminates expensive in-memory sort Use covered queries (projection) — Eliminates the FETCH stage — index serves the full queryA 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.Frequently asked questionsHow can I find slow queries in Azure DocumentDB?Use Diagnostic Logs routed to an Azure Log Analytics workspace. Query `VCoreMongoRequests` for long-running operations, then run `explain()` on a candidate query.What should I check in `explain("executionStats")`?Review `stage`, `totalDocsExamined`, `totalKeysExamined`, `nReturned`, and `executionTimeMillis`. `COLLSCAN` indicates a full collection scan, while `IXSCAN` means an index was used.Why might a single-field index still be inefficient?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.What is the ESR rule for compound indexes?ESR stands for Equality, Sort, Range. Place exact-match fields first, followed by the sort field and then range fields.How can I tell whether a sort is index-backed?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.What is a covered query?A covered query has all filter, sort, and projected output fields in the index. You can confirm coverage when `explain()` shows no `FETCH` stage.Should I create indexes for every possible query?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.