Validating AI-suggested indexes with explain
Parent: MongoDB Query Optimization Tooling: Compass Explain, Atlas Query Insights and Performance Advisor · Published reference · snapshot 2026-10-20 · skill ranked-92d6787e
↓ Facts as markdownall context files
Source-anchored research for Validating AI-suggested indexes with explain. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
How it works
- A read-path improvement must be weighed against index maintenance costs: every additional index uses storage and must be updated on writes, and excess indexes can add memory-cache pressure. Retain an index only when the recurring query patterns it serves justify those costs. (medium confidence) [source]
- Each additional index adds write-maintenance overhead and storage cost. Inserts and deletes maintain index keys, while updates may maintain affected indexes; weigh the read benefit of a suggested index against the collection's write workload and remove redundant or unused indexes when evidence supports doing so. (high confidence) [source]
How-to and procedures
- Run explain("executionStats") for the target operation before and after testing a candidate. Compare the winning plan and index with nReturned, totalKeysExamined, and totalDocsExamined; examine scan counts relative to results returned to see whether the candidate reduces avoidable work for that query. A COLLSCAN-to-IXSCAN change alone does not establish that the new index is efficient. (high confidence) [source]
Facts and statements
- MongoDB's Compass Assistant documentation illustrates a compound-index recommendation, { borough: 1, cuisine: 1 }, after its sample plan uses the cuisine_1 index and filters borough in memory. Atlas Performance Advisor describes Impact as an estimate derived from aggregated query-shape metrics. Together, these examples support treating an AI or advisor suggestion as a candidate to test against the application's real query mix, not as measured proof of end-to-end improvement. (medium confidence) [source]
- If the operation sorts results, inspect the full winning plan for a remaining SORT stage. A compound index may support filtering while its field order still fails to support the query's sort, so test the suggested field order against the actual filter and sort. (high confidence) [source]
- MongoDB's explain tutorial demonstrates using hint() with explain("executionStats") to compare candidate indexes. A hinted plan shows how a forced index behaves; rerun without hint() to see whether the normal planner selects it for the query. (high confidence) [source]
- Do not treat explain.executionStats.executionTimeMillis as production latency. MongoDB documents that explain bypasses the plan cache, that its elapsed time is not necessarily representative of actual query time, and that the metric excludes network transfer. Percona's plan-cache walkthrough shows that cached plans are reused and can become suboptimal as data distribution changes, so confirm improvements with representative workload measurements. (medium confidence) (verified as of 2026-10-20) [source]
- MongoDB documents comparing candidate indexes by running the same target query with explain("executionStats"), reviewing the winning plan, nReturned, totalKeysExamined, and totalDocsExamined, and using hint() to evaluate alternative indexes. Tencent's index guidance also recommends checking a recommended index with explain and scan-efficiency metrics. Applying this validation method to AI-suggested indexes is an operational extrapolation; these sources do not directly benchmark AI suggestions. (medium confidence) (verified as of 2026-10-19) [source]
- Do not treat IXSCAN alone as proof that an index improves performance: inspect the full plan and scanned-key/document counts relative to nReturned. A six-author study evaluating MongoDB 7.0.1 found cases where the query optimizer favored index scans even though collection scans ran faster. (medium confidence) (verified as of 2024-09-25) [source]
- MongoDB explain ignores the plan cache, and explain.executionStats.executionTimeMillis is not necessarily representative of steady-state query time. Use explain to diagnose plan shape and scan work, then corroborate latency under a representative workload or query-analytics data; Percona's PMM-QAN example compares query time and explain statistics before and after adding an index, but its demonstration used MongoDB 3.4.10 and PMM 1.11.0. (medium confidence) (verified as of 2026-10-20) [source]
Children
- No children recorded.