MongoDB Query Optimization Tooling: Compass Explain, Atlas Query Insights and Performance Advisor

Parent: MongoDB Performance Troubleshooting · Published reference · snapshot 2026-10-19 · skill atlas-diagnostics-expert/references/mongodb-query-optimization-tooling.md

Also known as: AI and LLM-Assisted MongoDB Query and Index Optimization, Atlas Performance Advisor, Atlas Performance Advisor (index and schema suggestions), Atlas Query Insights and Query Profiler, Atlas Search vs $text vs $regex (search offload decision), Compass Explain Plan Visualizer, Post-8.0 Query Engine Changes (8.1 and 8.2 rapid releases and 9.0)

↓ Facts as markdown all context files

Compass Explain Plan, Atlas Query Insights (Query Shapes, Namespace Insights, Query Profiler), Performance Advisor index, drop-index and schema suggestions, the 8.1–9.0 release-note changes to query observability, LLM-assisted tooling (Compass assistant, MongoDB MCP Server) and the $regex versus $text versus Atlas Search decision are the tooling surfaces around MongoDB query optimization. Compiled from MongoDB manual, Atlas and Compass pages plus the run's verified claims.

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.

Definitions

Since `{ a : 1 }` is a prefix of `{ a : 1, b : 1 }`, Performance Advisor suggests only `{ a : 1, b : 1 }`. To learn more about index prefixes, see [Prefixes.](https://www.mongodb.com/docs/manual/core/index-compound/#prefixes) [source]
Hidden indexes are not used to support queries. If you hide an index and observe substantial negative performance impact, consider keeping and unhiding the index so queries can resume using it. [source]
Since { a : 1 } is a prefix of { a : 1, b : 1 }, Performance Advisor suggests only { a : 1, b : 1 }. To learn more about index prefixes, see Prefixes. [source]
- A high Docs Examined : Returned Ratio means the query is scanning many documents to return few results, which can be a sign of an inefficient query or missing indexes. [source]
A COLLSCAN node means mongod scanned the whole collection document by document. The official tutorial's no-index example shows 0 index keys examined and documents examined equal to the collection size. After an index is added, examined keys approach returned documents. A large gap between examined and returned counts signals a missing or unselective index. [source]
`planCacheKey` is a hash of the [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape), further distinguished by any available indexes for the shape. [source]
- Operations that occurred during the period of lag are not replicated to one or more secondaries. If you're using replication to ensure data persistence, exceptionally long delays may impact the integrity of your data set. [source]
The intelligent assistant is an AI-powered tool designed for integrated MongoDB development support in MongoDB Compass. It answers natural language questions, assists with debugging errors, and provides guidance for performance optimization. [source]
A query shape hash is a string that uniquely identifies the query shape. An example query shape hash is `"F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"`. [source]
`queryShapeHash` is a hexadecimal string with the hash of a [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape). For details, see [Query Shapes.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) [source]
- Query Shape Reports are not available through the Atlas Administration API. [source]
A query shape is a set of specifications that group similar queries together. Specifications can include filters, sorts, projections, aggregation pipeline stages, a namespace, and others. Queries that have similar specifications have the same query shape. [source]
The $queryStats aggregation stage is unsupported and is not guaranteed to be stable in a future release. Don't build functionality that relies on a specific output format of this stage, since the output may change in a future release. [source]
$queryStats groups queries with common properties into the same output document. The resulting document is called a query stats entry. [source]
Query Insights is a section in the Atlas cluster sidebar that combines Namespace Insights (collection-level latency) and the upgraded cluster-centric Query Profiler; Query Shapes is the default tab. The launch blog is dated 2024-05-02. [source]

Structure and components

If you drop an index that is actively used in production, your application may incur a performance degradation. To ensure queries can still use an index during modification, you can create a temporary, redundant index that contains the same fields as the modified index. [source]
2. Create a temporary index that contains the `url` field [source]
- If `hasSort` is set to `true`, this indicates that the query contains a [`sort()`](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#mongodb-method-cursor.sort) method. Unindexed [`sort()`](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#mongodb-method-cursor.sort) methods can be very resource intensive. Check your search index configuration and confirm whether it supports the [`sort()`](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#mongodb-method-cursor.sort) method. [source]
Each index that the Performance Advisor suggests contains the following metrics. These metrics apply specifically to queries which would be improved by the index: [source]
- Text indexes can consume significant RAM. They contain one index entry for each unique stemmed word in each indexed field for each document. [source]
If query settings are set, `querySettings` contains details about the query settings that are applied to the query shape. [source]
The following `explain` examples contain queries with the same query shape: [source]
MongoDB 9.0 updates the $queryStats output structure to improve metrics organization. [source]
The order of the indexed fields impacts the effectiveness of a compound index. Compound indexes contain references to documents according to the order of the fields in the index. To create efficient compound indexes, follow the [ESR (Equality, Sort, Range) guideline.](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md#std-label-esr-indexing-guideline) [source]
Text indexes support [`$text`](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#mongodb-query-op.-text) [queries](https://www.mongodb.com/docs/manual/text-search.md#std-label-text-query) on fields that contain string content. They also support [aggregation expressions](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-qe-aggregation-operators) for encrypted string fields in [Queryable Encryption](https://www.mongodb.com/docs/manual/core/queryable-encryption.md#std-label-qe-manual-feature-qe) enabled collections. Text indexes improve performance when [source]
| Scenario | Performance insight | | --- | --- | | You run a query or aggregation without an index. | Add an index to support the operation. | | You run an aggregation pipeline that uses a [`$lookup`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup) stage. | Embed related data to avoid the need for a `$lookup` operation. | | You run a [`$text`](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#mongodb-query-op.-text) or [`$regex`](https://www.mongodb.com/docs/manual/reference/operator/query/regex.md#mongodb-query-op. [source]
Index filters are set with the [`planCacheSetFilter`](https://www.mongodb.com/docs/manual/reference/command/plancachesetfilter.md#mongodb-dbcommand-dbcmd.planCacheSetFilter) command and determine which indexes the planner evaluates for a [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape). A plan cache query shape consists of a combination of query, sort, and projection specifications. If an index filter exists for a given query shape, the planner only considers those indexes specified in the filter. [source]
The [Query Profiler](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler) contains several metrics you can use to pinpoint specific inefficient queries. You can visualize up to the past 24 hours of query operations. The Query Profiler can show the Examined : Returned Ratio (index keys examined to documents returned) of logged queries, which might help you identify the queries that triggered a [`Query Targeting: Scanned / Returned`](https://www.mongodb.com/docs/atlas/reference/alert-conditions.md#mongodb-alert-Query-Targeting--Scanned---Returned) alert. The ch [source]
The Query Profiler contains several metrics you can use to pinpoint specific inefficient queries. You can visualize up to the past 24 hours of query operations. The Query Profiler can show the Examined : Returned Ratio (index keys examined to documents returned) of logged queries, which might help you identify the queries that triggered a Query Targeting: Scanned / Returned alert. The chart shows the number of index keys examined to fulfill a query relative to the actual number of returned documents. [source]
This example matches documents where `runtime` is greater than `1000` and the indexed fields contain `baseball` or `colorado`. It sorts by ascending `year`, then descending relevance score: [source]
If the `movies` collection contains an index `{ year: 1, title: 1 }`, the following pipeline can use that index to find the first document of each group: [source]
1. The `movies` collection contains documents where the `metacritic` field is present in some documents and absent from others. Of the 21,349 documents, 6,964 have the `metacritic` field and 14,385 do not. [source]
For example, a pipeline that consists of [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match), [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort), [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) can benefit from indexes at every stage: [source]
Because the index contains all fields required by the query, MongoDB can both match the [query conditions](https://www.mongodb.com/docs/manual/tutorial/query-documents.md#std-label-read-operations-query-document) and return the results using only the index. [source]
Contains information on the selection of the query plan by the [query optimizer.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-read-operations-query-optimization) [source]
| Field | Description | Applicable Stages | | --- | --- | --- | | `docsExamined` | Specifies the number of documents scanned during the query execution stage. | `COLLSCAN`, `FETCH` | | `keysExamined` | For query execution stages that scan an index `keysExamined` is the total number of in-bounds and out-of-bounds keys that are examined in the process of the index scan. If the index scan consists of a single contiguous range of keys, only in-bounds keys need to be examined. If the index bounds consists of several key ranges, the index scan execution process may examine out-of-bounds keys in orde [source]
Starting in MongoDB 9.0, when you run `explain` in a mimumum of [executionStats](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain.md#std-label-explain-method-executionStats) verbosity mode, aggregation stages that use the classic query execution engine output a `expressionEvaluationPeakMemoryBytes` field. This field contains the peak memory usage, in bytes, from expression evaluation during stage execution. [source]
Query shapes exposing field names but redacted values may contain user PII (Personally Identifiable Information). To view truncated or full query shapes in Query Shape Insights, you must have one of the project roles listed above, which each give access to view PII (Personally Identifiable Information). For users without these permissions, Query Shape Insights identifies query shapes by their query shape hash. [source]
Starting in MongoDB 8.0, the pre-existing query shape is renamed to the plan cache query shape, and the queryHash field is renamed planCacheShapeHash. For the components of a command that differentiate plan cache query shapes, see the plan cache query shape definition. MongoDB 8.0 uses the new query shape and the existing plan cache query shape as shown on this page. [source]
the overall structure of the find, aggregation, and distinct commands, which support a wider range of query shapes than the existing plan cache query shape. [source]
A document that contains metrics for the [`$queryStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#mongodb-pipeline-pipe.-queryStats) aggregation stage. [source]
The Query Shape Insights table contains one row for each top query shape in your project, where top query shapes are determined by highest total execution time. Each row contains the query shape or its query shape hash, the namespace of the query shape, the query command, and any metrics that you select to display. You can use the Edit Columns button to choose which of the following metrics to include as table columns: [source]
Field values. $queryStats normalizes field values in a query to their data types (such as number or string) when the query is recorded. $queryStats never stores field values that contain user data. [source]
$queryStats returns an array of query stats entries. Some query stats entry properties contain literal values, and some properties are normalized to group common queries. [source]
The UTC time when $queryStats read this entry from the $queryStats virtual collection. asOf does not necessarily return the same UTC time for each result. Internally, the data structure is partitioned, and each partition will be read at an individual point in time. [source]
Contains the query shape and additional query attributes that group a set of queries together [source]
Contains attributes used to group similar queries together. For more information, see Query Shape. [source]
The key.queryShape document contains query shape fields. To learn about query shapes, see Query Shapes. [source]
| Document | Description | | --- | --- | | `key` | The unique combination of attributes that define an entry in the query stats output. The `key` contains attributes such as: [Query shape](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes); Client information; Read concern; [Collection type](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-collectionType) Each unique combination of attributes creates a separate entry in the `$queryStats` virtual collection. | | `asOf` | The UTC time when `$queryStats` read this [source]
The `key.queryShape` document contains query shape fields. To learn about query shapes, see [Query Shapes.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) [source]
When MongoDB logs `$queryStats` output, the results are redacted and anonymized. `$queryStats` output does not contain literals or field values. [source]
Messages related to $queryStats operations. To specify the log level for QUERYSTATS components, set the systemLog.component.queryStats.verbosity parameter. [source]
If a command with a specific query shape is rejected, MongoDB logs a message that states the query command was rejected. The message contains the query namespace, queryShapeHash, and the command with the rejected query. MongoDB only logs the message if the log verbosity level is set to at least 2. [source]
Messages related to [`$queryStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#mongodb-pipeline-pipe.-queryStats) operations. To specify the log level for `QUERYSTATS` components, set the [`systemLog.component.queryStats.verbosity`](https://www.mongodb.com/docs/manual/reference/configuration-options.md#mongodb-setting-systemLog.component.queryStats.verbosity) parameter. [source]
- the overall structure of the `find`, `aggregation`, and `distinct` commands, which support a wider range of query shapes than the existing plan cache query shape. [source]

How it works

Recommended indexes are accompanied by sample queries, grouped by [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape), that were run against a collection that would benefit from the suggested index. The Performance Advisor considers workload activity across all nodes in the replica set, including secondaries, when generating index recommendations. The Performance Advisor doesn't negatively affect the performance of your Atlas clusters. [source]
Each index suggestion includes an Average Query Targeting score indicating how many documents were read for every document returned for the index's corresponding query shapes. A score of 1 represents very efficient query shapes because every document read matched the query and was returned with the query results. All suggested indexes represent an opportunity to improve query performance. [source]
After you create a wildcard text index, when you insert or update documents, the index updates to include any new string field values. As a result, wildcard text indexes negatively impact performance for inserts and updates. [source]
- Text indexes impact write performance because MongoDB must add an index entry for each unique stemmed word in each indexed field of new documents. [source]
- Create the indexes that the Performance Advisor suggests when they align with your [Indexing Strategies.](https://www.mongodb.com/docs/manual/applications/indexes/) [source]
Building indexes when the target collection is under heavy write load can result in reduced write performance and longer index builds. [source]
When using the default [`maxNumActiveUserIndexBuilds`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.maxNumActiveUserIndexBuilds) of `3`, the total memory usage for all concurrent index builds can reach up to three times the value of `maxIndexBuildMemoryUsageMegabytes`. [source]
Guards on suggestions: no index with more than 16 fields; an index is suggested only if scanned documents minus returned documents exceeds 500 for impacted queries; overlapping candidates are de-duplicated so that prefix { a:1 } is dropped in favour of { a:1, b:1 }. [source]
Although indexes improve query performance, adding an index has negative performance impact for write operations. For collections with a high write-to-read ratio, indexes are expensive because each insert and update must also update any indexes. [source]
You can also adjust the time range the Performance Advisor takes into account when suggesting indexes by using the Time Range dropdown at the top of the Performance Advisor. [source]
You can create [indexes](https://www.mongodb.com/docs/manual/core/indexes/) suggested by the Performance Advisor directly within the Performance Advisor itself. When you create indexes, keep the ratio of reads to writes on the target collection in mind. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. To learn more about indexing strategies, see [Indexing Strategies.](https://www.mongodb.com/docs/manual/applications/indexes/) [source]
You can create indexes suggested by the Performance Advisor directly within the Performance Advisor itself. When you create indexes, keep the ratio of reads to writes on the target collection in mind. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. To learn more about indexing strategies, see Indexing Strategies. [source]
Building an index in a rolling fashion reduces the resiliency of your cluster and increases index build times. We only recommend using rolling index builds when regular index builds do not meet your needs. [source]
If you decide to continue with a rolling index, consider that they must meet certain conditions to succeed. To ensure your index build succeeds, avoid the following design patterns that commonly trigger a restart loop: [source]
Atlas automatically cancels rolling index builds that don't succeed on all nodes. When a rolling index build completes on some nodes, but fails on others, Atlas cancels the build and removes the index from any nodes that it was successfully built on. [source]
When an index build completes, Atlas generates an [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed.md#std-label-view-activity-feed) and sends a notification email to the project owner with the following information: [source]
- When building large text indexes, ensure sufficient file descriptor limits. See [recommended settings.](https://www.mongodb.com/docs/manual/reference/ulimit.md#std-label-ulimit) [source]
- Text indexes store individual words, not multi-word strings or word proximity information. Queries with multiple words run faster when the entire collection fits in RAM. [source]
When explain is run in either executionStats or allPlansExecution verbosity mode, certain aggregation stages that may spill to disk have additional output. These stages include $sort, $group, $setWindowFields, $bucketAuto, $graphLookup, and $lookup (when using the slot-based execution engine). Starting in MongoDB 8.1, this also includes $text queries that use the TextOr execution stage. [source]
As with any hash function, two different plan cache query shapes may result in the same hash value. However, the occurrence of hash collisions between different plan cache query shapes is unlikely. [source]
COMPASS-6496 reported that 'Actual query execution time' for aggregations was inflated because it summed per-stage times that already included earlier stages. It was reported on Compass 1.35.0 with MongoDB 6.0.4 and fixed in Compass 1.38.0 (resolved 2023-06-07). Older Compass builds may over-report aggregation time. [source]
Enabling Queryable Encryption on a collection redacts fields from some diagnostic commands and omits some operations from the query log. This limits the data available to MongoDB support engineers, especially when analyzing query performance. To measure the impact of operations against encrypted collections, use a third party application performance monitoring tool to collect metrics. [source]
When you use Queryable Encryption, you define encrypted fields at the collection level using an encryption schema. Encrypting a field and enabling queries increases storage requirements and impacts query performance. You can configure an encrypted field for either equality or range queries, but not both. Configure fields for the expected query type. [source]
Enabling [`reportOpWriteConcernCountersInServerStatus`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.reportOpWriteConcernCountersInServerStatus) can have a negative performance impact; specifically, when running *without* TLS. [source]
The default values for the diagnostic data capture interval and the maximum sizes are configured to provide data to MongoDB engineers with minimal impact on performance and storage size. Typically, these values need modification only when requested by MongoDB engineers for specific diagnostic purposes. [source]
A document that shows how often threads use certain internal server locks and how often these locks cause threads to wait. This document focuses on a small set of important, long-lived locks that coordinate core server activities, such as global server state, replication, and connection management. Contention on these locks has a noticeable impact on performance. Use these metrics to identify when coordination points become bottlenecks, such as when contention and wait time increase alongside slower operations or replication lag. [source]
Rolling indexes may negatively impact your deployment. For information on when to use this index build, see [Rolling Index Builds.](https://www.mongodb.com/docs/manual/core/rolling-index-builds.md#std-label-rolling-index-build) [source]
Text indexes support `$text` queries on fields containing string content. Text indexes improve performance when searching for specific words or multi-word strings within string content. [source]
- When an [index filter](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) exists for the query shape, MongoDB ignores the [`hint()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) [source]
Because the query shapes are the same, the explain output has the same queryShapeHash for each of the examples, and the same planCacheShapeHash for each. For example: [source]
Because the query shape is different from the shape in the previous section, the explain output has a different queryShapeHash from the previous example, and a different planCacheShapeHash. For example: [source]
The Explain Plan doesn't show aggregation pipeline stages such as [`$merge`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#mongodb-pipeline-pipe.-merge) and [`$out`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#mongodb-pipeline-pipe.-out) because Atlas ignores all out stages from the aggregation before running the explain plan. [source]
Because the values in the `query` predicate are insignificant in determining the plan cache query shape, the following command would also remove the second index filter: [source]
When Atlas determines that your schema or queries can be improved, it displays a performance insight. Performance insights show ways to improve your schema and data modeling practices. Use performance insights to learn best schema design practices and improve application performance. [source]
Query optimization can occur both during development and later as your data usage and demand changes. As collections grow, a periodic review of query performance can help determine when clusters need to scale up or scale out. [source]
1. When a project becomes eligible, Atlas sends the first report approximately one hour after the [`Query Targeting: Scanned Objects / Returned`](https://www.mongodb.com/docs/atlas/reference/alert-conditions.md#mongodb-alert-Query-Targeting--Scanned-Objects---Returned) alert. [source]
Turn the toggle on to subscribe or off to unsubscribe. When subscribing for the first time, select the cluster or clusters that you want the report to cover. The change applies to all [`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) users in the project. [source]
- A sort on `{ <timeField>: ±1 }` uses a secondary index on `{ metaField: ±1, timeField: ±1 }` when there is a point predicate on `<metaField>`. [source]
Last point queries are most performant when they use the [DISTINCT\_SCAN optimization](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results). This optimization is only available when an index on `timeField` is descending. [source]
Index hints cause MongoDB to use a specific index for a query. Some operations on time series collections can only take advantage of an index if that index is specified in a hint. [source]
For example, the following query causes MongoDB to use the `timestamp_1_metadata.sensorId_1` index: [source]
Specifies the maximum number of lock timeouts per second when trying to write to [`profile`](https://www.mongodb.com/docs/manual/reference/system-collections.md#mongodb-data--database-.system.profile) collections. Once this threshold is reached, profiling is disabled for this database. This setting lasts until you manually re-enable profiling. [source]
- [`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined) displays `3809` to indicate that MongoDB scanned 3,809 index entries. The number of keys examined match the number of documents returned, meaning that the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) only had to examine index keys to return the results. The [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) did not have to scan all [source]
When run with an index, the query scans only as many index entries and documents as it returns, resulting in a more efficient query. [source]
- When possible, put [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) at the beginning of the pipeline to use indexes that scan the matching documents in a collection. [source]
The [default](https://www.mongodb.com/docs/atlas/configure-alerts.md#std-label-default-alert-settings) [`Query Targeting: Scanned Objects / Returned`](https://www.mongodb.com/docs/atlas/reference/alert-conditions.md#mongodb-alert-Query-Targeting--Scanned-Objects---Returned) alert ratio differs slightly. The ratio of the average number of documents scanned to the average number of documents returned during a sampling period triggers this alert. [source]
The optimized index build performance is at least on par with background index builds. For workloads with few or no updates received during the build process, optimized index builds can be as fast as a foreground index build on that same data. [source]
Because inaccessible configuration servers can seriously impact the availability of a sharded cluster, you should monitor your configuration servers to ensure that the cluster remains well balanced and that [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instances can restart. [source]
| Atlas Cluster Operations and Connection Metrics | Opcounters; Operation Execution Times; Query Executors and Query Targeting; Connections; Queues; Scan and Order | | --- | --- | | Hardware Metrics | Normalized System CPU; Normalized Process CPU; Disk Latency; Disk IOPS; Disk Space Free; System Memory; Swap Usage | | Replication Metrics | Replication Lag; Replication Oplog Window; Replication Headroom; Oplog GB/Hour; Opcounters -repl | [source]
As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions between different query shapes is unlikely. [source]
Starting in MongoDB 8.3, if the FCV (feature compatibility version) is 8.3 or later, MongoDB backfills the `representativeQuery` field for query settings that you set with a query shape hash. When MongoDB runs a query that matches the query shape, it schedules the backfill. The backfill requires no action from you. [source]
Because the database profiler writes data to the system.profile collection in a database, the profiler profiles some write activity, even for databases that are otherwise read-only. [source]
Atlas Infinite clusters only support this command when the [database profiler level](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) is `0`. [source]
- a [filter](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-options-filter) of `{ op: "query", millis: { $gt: 2000 } }`, which causes the profiler to only record `query` operations that took longer than 2 seconds. [source]
Because the profiling level is `0`, the profiler is disabled and does not record matching operations to the profiler collection. The filter still applies to the diagnostic log. [source]
You can set a `filter` to control which operations are logged by the profiler. When set, any configured filters are also returned by [`db.getProfilingStatus()`](https://www.mongodb.com/docs/manual/reference/method/db.getprofilingstatus.md#mongodb-method-db.getProfilingStatus), along with a `note` explaining filter behavior. [source]
Number of times this MongoDB process failed to store a new query stats key. Generally, these failures happen when the `$queryStats` virtual collection runs out of space. [source]
Number of times this MongoDB process failed to compute a one-way tokenized query stats key when `$queryStats` was called with the `transformIdentifiers` option. [source]
When set to level `2`, the profiler ignores user provided values for `slowms` and `filter`. [source]
Because $queryStats reports only the operations that MongoDB samples, its output represents a subset of the operations run against your deployment. Counts and execution times reflect only the sampled operations. [source]
When an HMAC key is specified to the transformIdentifiers option, $queryStats uses the HMAC key to apply an HMAC-SHA-256 hash function on the following data: [source]
In sharded clusters, MongoDB records query statistics separately on each mongod and mongos when $queryStats is enabled. Shard servers also record statistics for queries that originate from mongos. These provide more complete shard-level metrics for queries that users route through mongos. [source]
When Atlas runs $queryStats on your deployment, it uses a unique HMAC key per Atlas organization to transform your data and avoid collecting sensitive information. [source]
Because the database profiler writes data to the [`system.profile`](https://www.mongodb.com/docs/manual/reference/system-collections.md#mongodb-data--database-.system.profile) collection in a database, the profiler profiles some write activity, even for databases that are otherwise read-only. [source]
The following examples show the differences in log messages when `$queryStats` output is included. [source]
The Query Profiler samples operations by using a bucketing algorithm that adjusts the sample size based on the width of the selected time range. Because of differences in bucket size, a wider time range might display fewer sampled operations than a narrower time range. For exact, unsampled operation counts, download your `mongod` logs. To learn more, see [View and Download MongoDB Logs.](https://www.mongodb.com/docs/atlas/mongodb-logs.md#std-label-mongodb-logs) [source]

How-to and procedures

To establish recommended indexes, the Performance Advisor uses these metrics in a formula to calculate the Impact, or performance improvement that creating an index matching that query shape would cause. When the Performance Advisor suggests indexes, it ranks the indexes by their Impact. The Impact is determined by the total wasted bytes read. [source]
To learn more about the command syntax and parameters, see the Atlas CLI documentation for [atlas performanceAdvisor namespaces list.](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceAdvisor-namespaces-list/) [source]
To learn more about the command syntax and parameters, see the Atlas CLI documentation for [atlas performanceAdvisor slowQueryLogs list.](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceAdvisor-slowQueryLogs-list/) [source]
To enable or disable Performance Advisor and the Query Profiler for a project, you must have the [`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) role for the project or the [`Organization Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Organization-Owner) role on its parent organization. [source]
To enable or disable Performance Advisor and the Query Profiler for a project, you must have the Project Owner role for the project or the Organization Owner role on its parent organization. [source]
To learn more about the command syntax and parameters, see the Atlas CLI documentation for [atlas performanceAdvisor suggestedIndexes list.](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceAdvisor-suggestedIndexes-list/) [source]
To view the indexes on the `siteAnalytics` collection, run this command: [source]
To learn how to use the MongoDB MCP Server to get Performance Advisor recommendations, see [Access Performance Advisor with the MongoDB MCP Server.](https://www.mongodb.com/docs/atlas/performance-advisor/access-with-mcp.md#std-label-performance-advisor-mcp) [source]
To learn more about the performance advisor MCP tool, see [Performance Advisor Tool.](https://www.mongodb.com/docs/mcp-server/tools.md#std-label-mcp-pa-tool) [source]
To analyze query performance, see [Interpret Explain Plan Results.](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md) [source]
To learn how to create indexes that the Performance Advisor suggests, see [Create Suggested Indexes.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-pa-create-suggested-indexes) [source]
To drop or hide an index by using the MongoDB Shell, paste and run the command provided by the Performance Advisor. [source]
To access the intelligent assistant in MongoDB Compass click the sidebar panel with the . This opens the assistant, where you can ask a variety of questions related to your MongoDB Compass connection errors, query outputs, explain plan statistics, and more. [source]
To learn how to create indexes that the Performance Advisor suggests, see Create Suggested Indexes. [source]
Use collation to specify language-specific rules for string comparison, such as rules for lettercase and accent marks. The [collation document](https://www.mongodb.com/docs/manual/reference/collation/#collation-document) contains a `locale` field which indicates the [ICU Locale code](https://unicode-org.github.io/icu/userguide/locale/), and may contain other fields to define collation behavior. [source]
To review the list of locales that MongoDB collation supports, see the [list of languages and locales](https://www.mongodb.com/docs/manual/reference/collation-locales-defaults.md#std-label-collation-languages-locales). To learn more about collation options, including which are enabled by default for each locale, see [Collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation-document-fields) in the MongoDB manual. [source]
To learn more about rebuilding indexes, see [Build Indexes on Replica Sets.](https://www.mongodb.com/docs/manual/tutorial/build-indexes-on-replica-sets/) [source]
To help you better understand the performance of your pipeline, you can view your pipeline's explain plan. You can view the explain plan at any point while creating or editing your pipeline. [source]
To help you better understand the performance of your query, you can view your query's explain plan. [source]
To the left of the Query Performance Summary, MongoDB Compass displays the query stages FETCH and IXSCAN. IXSCAN indicates that the mongod used an index to satisfy the query before executing the FETCH stage and retrieving the documents. [source]
To analyze query performance, see Interpret Explain Plan Results. [source]
To learn more about the command syntax and parameters, see the Atlas CLI documentation for [atlas performanceAdvisor slowOperationThreshold disable.](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceAdvisor-slowOperationThreshold-disable/) [source]
To learn more about the command syntax and parameters, see the Atlas CLI documentation for [atlas performanceAdvisor slowOperationThreshold enable.](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceAdvisor-slowOperationThreshold-enable/) [source]
To view the MongoDB 8.0 `queryShapeHash` and `planCacheShapeHash` hexadecimal strings, you can use the [`explain`](https://www.mongodb.com/docs/manual/reference/command/explain.md#mongodb-dbcommand-dbcmd.explain) command. [source]
To learn more about the impact of unnecessary indexes, see [Remove Unnecessary Indexes.](https://www.mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/) [source]
To view field values in a sample query in the Performance Advisor, you must have Project Observability Viewer, Project Data Access Read Only, Project Data Access Read/Write, or Project Data Access Admin access to the project. [source]
To prevent negative performance impact from the balloon driver and memory overcommitment features, reserve the full amount of memory for the virtual machine running MongoDB. Reserving the appropriate amount of memory for the virtual machine prevents the balloon from inflating in the local operating system when there is memory pressure in the hypervisor. [source]
To optimize your indexes, we recommend deploying [dedicated Search nodes](https://www.mongodb.com/docs/atlas/cluster-config/multi-cloud-distribution.md#std-label-configure-search-nodes), which allows you to scale your Atlas cluster and [`$search`](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search) workloads independently. Dedicated Search Nodes only run the `mongot` process and therefore improve the availability, performance, and workload balancing of the `mongot` process. [source]
To improve query performance, you can create a [compound text index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/create-text-index.md#std-label-compound-text-index-example) and include an equality match in your `$text` queries. If the compound index contains the field used in your equality match, the index scans fewer entries and returns results faster. [source]
To view your full explain plan as raw JSON, select the Raw Output view. [source]
Set an [index filter](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) for a collection. If an index filter already exists for the [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape), the command overrides the previous index filter. [source]
To see whether MongoDB will apply an index filter for a plan cache query shape, check the [`indexFilterSet`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.indexFilterSet) field of either the [`db.collection.explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain.md#mongodb-method-db.collection.explain) or the [`cursor.explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain.md#mongodb-method-cursor.explain) method. [source]
To learn about important explain result fields and how to interpret them, see [Interpret Explain Plan Results.](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#std-label-interpret-explain-plan) [source]
To view query performance in the Query Profiler, you must have [`Project Read Only`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Read-Only) or [`Project Observability Viewer`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Observability-Viewer) access to the project. [source]
To learn about important explain result fields and how to interpret them, see Interpret Explain Plan Results. [source]
To view query performance in the Query Profiler, you must have Project Read Only or Project Observability Viewer access to the project. [source]
To sort by a metric in descending or ascending order, click the sort icon next to the associated column name once or twice, respectively. To sort by multiple columns, press Shift and click the sort icons in the order in which you want to multi-sort the table. [source]
Create a secondary single-field index on the `timestamp` field: [source]
To confirm that the sort operation used the Secondary Index, run the operation again with the `explain` option: [source]
To confirm that the last point query used the secondary index, run the operation again using `explain`: [source]
To confirm that the sort operation used the Secondary Index, run the operation again with the `.explain( "executionStats" )` option: [source]
To confirm that the last point query used the secondary index, run the operation again using `.explain( "executionStats" )`: [source]
To view the query plan selected, chain the [`cursor.explain("executionStats")`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain.md#mongodb-method-cursor.explain) cursor method to the end of the **find** command: [source]
To support the query on the `year` field, add an index on the `year` field: [source]
To view the query plan statistics, use the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain.md#mongodb-method-cursor.explain) method: [source]
To support the query, add a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound). With compound indexes, the order of the fields matter. [source]
To determine whether a pipeline uses indexes, review the query plan and look for `IXSCAN` or `DISTINCT_SCAN` plans. [source]
Use dry-run mode to evaluate the impact of a threshold before you enforce it. [source]
To estimate the storage that prefix, suffix, and substring queries require before you configure them, see [Estimate Queryable Encryption Storage Impact.](https://www.mongodb.com/docs/manual/core/queryable-encryption/qe-estimate-storage-impact.md#std-label-qe-estimate-storage-impact) [source]
Add the estimated disk and memory impact to your current collection size, then compare the totals against the storage and memory available in your deployment. [source]
Use `kyber` to run multiple workloads in the same VM or in your own data center. `kyber` improves the I/O interpolation under contention, and reduces the impact of noisy neighbors. Additionally, `kyber` works efficiently in self hosted situations, such as on-premises virtualization and collocated workloads in one large cloud VM. However, `kyber` is only available on linux kernels starting in version 4.12. [source]
To monitor your cluster or database performance, you can view the cluster metrics such as historical throughput, performance, and utilization metrics. The following table lists some (but not all) important categories of metrics to monitor: [source]
Use this endpoint to reject a query shape (preventing it from executing on the cluster) or to unreject a previously rejected query shape (allowing it to execute again). This operation is idempotent: rejecting an already rejected query shape or unrejecting an already unrejected query shape will return 200 OK. [source]
To add query settings for a query shape, use setQuerySettings. [source]
To block a query shape, use an operation rejection filter. [source]
To view metrics with Query Shape Insights, you must have Project Observability Viewer, Project Data Access Read Only, Project Data Access Read/Write, Project Data Access Admin, or Project Owner access to the project. [source]
To view more details about a query shape, click on the row for the query shape to open the Query Shape Details view. This view contains the full query shape and query shape hash, each table metric listed above, and the following additional metrics: [source]
To run $queryStats without the transformIdentifiers option, the user must have the queryStatsRead privilege action. [source]
To run $queryStats with the transformIdentifiers option, the user must have the both the queryStatsRead and queryStatsReadTransformed privilege actions. [source]
To learn how to control $queryStats logging behavior, see Toggle $queryStats Log Output. [source]
To use the MongoDB Node.js driver to add a $queryStats stage to an aggregation pipeline, use the $queryStats operator in a pipeline object. [source]
To learn how to control `$queryStats` logging behavior, see [Toggle $queryStats Log Output.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats/toggle-logging.md#std-label-queryStats-toggle-logging) [source]
To see the query shapes for which cached query plans exist, see [Examples.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#std-label-planCacheStats-examples) [source]
To delete query settings, you must provide either a [query shape](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) hash string or a query shape to `removeQuerySettings`. [source]
To find a hash string or query shape, you can use a [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) stage in an aggregation pipeline. The hash string is named `queryShapeHash` in the `$querySettings` output. [source]

Examples and snippets

````text — `## Summary` … [source]
```javascript — `"t":{` … [source]
```javascript — `"t": {` … [source]
```javascript — `db.runCommand(` … [source]
```javascript — `db.runCommand(` … [source]
```javascript — `db.runCommand(` … [source]
[ — `{ "$match": { "borough": "Manhattan", "cuisine": "Italian" } },` … [source]
```javascript — `db.pizzaOrders.explain().find(` … [source]
```javascript — `db.pizzaOrders.explain().find(` … [source]
```text — `# Return a JSON-formatted list of suggested indexes for the atlas-111ggi-shard-00-00.111xx.mongodb.net:27017 h` … [source]
```javascript — `metrics : {` … [source]
```javascript — `"was" : 2,` … [source]
```text — `Interpret this explain plan for me.` … [source]
```javascript — `"t":{"$date":"2026-08-14T13:36:19.194-04:00"},"s":"D1",` … [source]
```javascript — `"t": { "$date": "2021-03-17T20:30:07.212+01:00" },` … [source]
```javascript — `"t": {` … [source]
```javascript — `id: '<object-id>',` … [source]
```csharp — `await _collection?.Indexes.CreateOneAsync(new CreateIndexModel<BsonDocument>(` … [source]
```csharp — `// Indexes on ``timeField`` descending are more performant because they` … [source]
```csharp — `_collection?.Indexes.CreateOne(` … [source]
```text — `"explainVersion": "1",` … [source]
```csharp — `_collection?.Indexes.CreateOne(new CreateIndexModel<BsonDocument>(` … [source]
```java — `collection.createIndex(Indexes.ascending("timestamp"));` … [source]
```java — `// Indexes on ``timeField`` descending are more performant because they` … [source]
```java — `collection.createIndex(Indexes.compoundIndex(` … [source]
```java — `collection.createIndex(Indexes.geo2dsphere("metadata.location"));` … [source]
```javascript — `// Indexes on ``timeField`` descending are more performant because they` … [source]
```python — `# Indexes on ``timeField`` descending are more performant because they` … [source]
```javascript — `queryPlanner: {` … [source]
```javascript — `db.adminCommand( {` … [source]
```sh — `atlas performanceAdvisor suggestedIndexes list [options]` … [source]
```javascript — `db.runCommand(` … [source]
```javascript — `db.adminCommand( {` … [source]
```javascript — `profiler: {` … [source]
```javascript — `db.adminCommand( {` … [source]
```javascript — `db.getSiblingDB("admin").aggregate( [` … [source]
```javascript — `db.getSiblingDB("admin").aggregate( [` … [source]
```javascript — `"t":{"$date":"2026-08-14T13:36:19.194-04:00"},"s":"D1",` … [source]
```javascript — `db.adminCommand( {` … [source]
```text — `# Return a JSON-formatted list of log lines for collections with slow queries for the atlas-111ggi-shard-00-00` … [source]
```text — `# Return a JSON-formatted list of namespaces for collections with slow queries for the atlas-111ggi-shard-00-0` … [source]

Measurements and reference values

The advisor monitors operations slower than 100 ms (docs for index ranking) and groups them into query shapes. In the UI the threshold is Atlas-managed (dynamic) by default and can be switched to a fixed value; MongoDB advises not setting a fixed threshold below 100 ms. Changing it needs Project Owner. [source]
9.0 enables $queryStats collection by default for reads and writes by sampling: internalQueryStatsSampleRate and internalQueryStatsWriteCmdSampleRate both default to 0.01 (1%), so output is a subset of operations. Update, insert and delete commands now get query shapes (8.1 added count and distinct). [source]
- On clusters running MongoDB 9.0 or later, Atlas randomly samples 1% of read and write operations by default. Displayed metric values reflect only sampled operations, approximately 1% of total operations. Query shapes that run infrequently may not appear in the results. This method also collects write operations (`insert`, `update`, and `delete`), which appear in Query Shape Insights alongside read operations. [source]
Starting in MongoDB 9.0, $queryStats collects stats for a sample of 1% of read and write operations by default. To change the sampling rate, set the internalQueryStatsSampleRate parameter for read operations or the internalQueryStatsWriteCmdSampleRate parameter for write operations. Both parameters accept a value between 0.0 and 1.0. [source]
This alert might appear when Atlas automatically [upgrades your search indexes](https://www.mongodb.com/docs/search/performance/index-performance.md#std-label-index-create-and-update) to enable new features. Your cluster must have sufficient disk space for both the previous and new version of the index. If the rebuild increases disk space use to 90% or more, Atlas increases the cluster storage if you enabled auto-scaling. After the index upgrade completes, Atlas deletes the old version of the index, which frees up disk space. [source]
Query Shape Insights (Query Shapes tab) requires a dedicated cluster (M10+) on MongoDB 8.0+. On 9.0+ Atlas randomly samples 1% of reads and writes by default, so shown metrics reflect about 1% of operations, infrequent shapes may be missing, and totals shift after an 8.x to 9.0 upgrade because of the sampling change. [source]

Problems, failure modes and limitations

You can use the MongoDB MCP server to get [Performance Advisor](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-performance-advisor) recommendations from your preferred AI tools by using natural language. These recommendations include retrieving slow queries, performing index management, and suggesting schema improvements. [source]
The MongoDB MCP Server exposes an `atlas-get-performance-advisor` tool (suggested indexes, drop-index suggestions, slow query logs, schema advice) plus database tools `explain`, `collection-indexes`, `create-index` and `drop-index`, letting AI clients such as Claude, Cursor and GitHub Copilot drive index analysis in natural language. [source]
To learn more about the MongoDB Atlas Performance Advisor, see [Monitor and Improve Slow Queries.](https://www.mongodb.com/docs/atlas/performance-advisor/) [source]
Via MCP, slow-query analysis is capped at a sample of the 50 most recent slow queries matching the prompt; this cap does not apply to the Performance Advisor UI in Atlas. [source]
The Performance Advisor's recommendations come from MongoDB's own slow-query analysis; the LLM layer (MCP or Compass assistant) is an interface to those endpoints (suggestedIndexes, dropIndexSuggestions, schemaAdvice), not a separate LLM-based recommender. [source]
Performance Advisor is only available on M10+ clusters and Flex clusters; the Atlas-managed slow-query threshold is disabled on Free (M0) clusters and unsupported on clusters with MongoDB Search enabled. [source]
Schema suggestions flag anti-patterns: unbounded arrays, too many collections, unnecessary indexes, excessively large (bloated) documents, and excessive $lookup use. They come from sampling documents in the most active collections and collections with slow queries, exposed via the Admin API listSchemaAdvice endpoint (Public Preview, Project Read Only role). [source]
Access control: Project Read Only sees slow queries and suggested indexes; seeing field values in sample queries needs a data-access role (Observability Viewer, Data Access Read Only/Read-Write/Admin), otherwise values are redacted. Creating an index from the advisor needs Data Explorer enabled for the project. [source]
For deployments hosted in MongoDB Atlas, you can create and manage indexes with the MongoDB Atlas UI or the Atlas CLI. MongoDB Atlas also includes a Performance Advisor that recommends indexes to improve slow queries, ranks suggested indexes by impact, and recommends which indexes to drop. [source]
The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. The threshold for slow queries varies based on the average time of operations on your cluster to provide recommendations pertinent to your workload. [source]
Returns suggested indexes and slow query data for a database deployment. [source]
| Method | Availability | Description | | --- | --- | --- | | Use the Atlas Performance Advisor | M10+ Atlas clusters | The Atlas Performance Advisor monitors slow queries and suggests new indexes to improve performance. For more information, see [Monitor and Improve Slow Queries with the Performance Advisor.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-performance-advisor) | | Check ongoing operations in Atlas | M10+ Atlas clusters | You can use the [Atlas Real-Time Performance Panel](https://www.mongodb.com/docs/atlas/real-time-performance-panel.md#std-label-real-time [source]
The Performance Advisor automatically analyzes logs for slow-running queries and recommends indexes to create and drop. It analyzes slow queries and provides index suggestions for individual collections, ranked by a calculated impact score, and tailored to your workload. This gives you an easy, instantaneous way to make high-impact performance improvements. We recommend that you monitor regularly, focus on slow queries, and enable the profiler selectively to minimize overhead. [source]
You can return log lines for slow queries that the Performance Advisor identifies from the Atlas Administration API with [Return Slow Queries](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/operation/operation-listslowqueries). To return suggested indexes and more with the Atlas Administration API, see [Performance Advisor.](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/group/endpoint-performance-advisor) [source]
The [Performance Advisor](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-pa-slow-queries) provides the easiest and quickest way to create an index. The Performance Advisor monitors queries that MongoDB considers slow and recommends indexes to improve performance. Atlas dynamically adjusts your slow query threshold based on the execution time of operations across your cluster. [source]
It is possible to receive a Query Targeting alert for an inefficient query without receiving index suggestions from the Performance Advisor if the query exceeds the slow query threshold and the ratio of scanned to returned documents is greater than the threshold specified in the alert. [source]
| Use Case | Example Prompts | Performance Advisor Operation | | --- | --- | --- | | Analyze Slow Queries | `Show me my slow queries``What is slowing down my cluster?``Show me queries that are longer than 5 seconds``Show me slow writes in the website.users namespace` | [slowQueryLogs](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/operation/operation-listgroupprocessperformanceadvisorslowquerylogs) | | Index Suggestions | `Are there any indexes I should create to improve performance?``What indexes do you recommend I drop?` | [suggestedIndexes](https://www.mongodb.com/docs/api/doc/atla [source]
| Name | Type | Required | Description | | --- | --- | --- | --- | | --duration | int | false | Length of time in milliseconds for which you want to return results. If you specify the since option, the duration starts at the date and time specified. If you don't set the since option, this command returns data from the duration before the current time. | | -h, --help | | false | help for list | | --nLog | int | false | Maximum number of log lines to return. This value defaults to 20000. | | --namespaces | strings | false | Namespaces from which to retrieve suggested slow query logs formatted a [source]
The backfill runs asynchronously on a best-effort basis to limit the performance impact on your queries. A query setting isn't guaranteed to be backfilled the first time a matching query runs. If a backfill doesn't complete, MongoDB attempts it again the next time a matching query runs. [source]
To return up to 20 namespaces in `<database>.<collection>` format for collections experiencing slow queries using the Atlas CLI, run the following command: [source]
8.2 standardized explain output for disk-spilling stages with consistent fields spills, spilledBytes, spilledRecords and spilledDataStorageSize, added delinquent-ticket and cpuNanos metrics to $queryStats, and lists 'reduced query multi-planning costs' among performance improvements. The 8.2 notes caveat that gains vary by workload. [source]
| Method | Availability | Description | | --- | --- | --- | | Use the Atlas Query Profiler | M10+ Atlas clusters | The Atlas Query Profiler shows long-running operations and performance statistics. For more information, see [Monitor Query Performance with the Query Profiler.](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-profile-database) | | Enable the Database Profiler | Atlas clusters and self-hosted deployments | When enabled, the database profiler stores information about slow queries in the [`system.profile`](https://www.mongodb.com/docs/manual/reference/system- [source]
To view collections with slow queries and see suggested indexes, you must have [`Project Read Only`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Read-Only) access or higher to the project. [source]
Return the suggested indexes for collections experiencing slow queries. [source]
- [atlas api performanceAdvisor listSlowQueryLogs](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listslowquerylogs.md#std-label-atlas-api-performanceAdvisor-listSlowQueryLogs) - Returns log lines for slow queries that the Performance Advisor and Query Profiler identified. [source]
The slow operation threshold determines which operations are flagged by the Performance Advisor and Query Profiler. When enabled, the application uses the average execution time for operations on your cluster to determine slow-running queries. As a result, the threshold is more pertinent to your cluster workload. Application-managed slow operation threshold is enabled by default for dedicated clusters (M10+). [source]
Returns log lines for slow queries that the Performance Advisor and Query Profiler identified. [source]
- [Monitor and improve slow queries](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-performance-advisor) with the Performance Advisor. [source]
You can use the Atlas UI, Atlas CLI, and the Atlas Administration API to view slow queries and suggestions for improving the performance of your queries from the Performance Advisor. [source]
| Name | Type | Required | Description | | --- | --- | --- | --- | | --clusterName | string | true | Human-readable label that identifies the cluster. | | --commands | & | false | Retrieve query shape statistics matching specified MongoDB commands. To include multiple commands, pass the parameter multiple times delimited with an ampersand (&) between each command. The currently supported parameters are: find, distinct, aggregate, count, insert, update, and delete. The insert, update, and delete commands return results only for clusters running MongoDB 9.0 or later with write query shape statis [source]
/ Analyze Slow Queries / Performance Advisor [source]
Docs Home / Management / Monitor Clusters / Analyze Slow Queries / Performance Advisor [source]
The slow operation threshold determines which operations are flagged by the Performance Advisor and Query Profiler. When disabled, the application considers any operation that takes longer than 100 milliseconds to be slow. [source]
- [atlas performanceAdvisor suggestedIndexes](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceadvisor-suggestedindexes.md#std-label-atlas-performanceAdvisor-suggestedIndexes) - Get suggested indexes for collections experiencing slow queries [source]
The threshold determines which operations the Performance Advisor and Query Profiler considers slow. When enabled, MongoDB Cloud uses the average execution time for operations on your cluster to determine slow-running queries. As a result, the threshold is more pertinent to your cluster workload. The slow operation threshold is enabled by default for dedicated clusters (M10+). When disabled, MongoDB Cloud considers any operation that takes longer than 100 milliseconds to be slow. [source]
Monitor and Improve Slow Queries with the Performance Advisor [source]
To view collections with slow queries and see suggested indexes, you must have Project Read Only access or higher to the project. [source]
Changing the threshold for slow operations using [`db.setProfilingLevel`](https://www.mongodb.com/docs/manual/reference/method/db.setProfilingLevel.md#mongodb-method-db.setProfilingLevel) can impact performance and system log settings. Carefully consider any performance and security implications before you use [`db.setProfilingLevel`](https://www.mongodb.com/docs/manual/reference/method/db.setProfilingLevel.md#mongodb-method-db.setProfilingLevel) to adjust the Query Profiler settings on a production deployment. Query Profiler settings reset to default values following a node restart. [source]
Changing the threshold for slow operations using db.setProfilingLevel can impact performance and system log settings. Carefully consider any performance and security implications before you use db.setProfilingLevel to adjust the Query Profiler settings on a production deployment. Query Profiler settings reset to default values following a node restart. [source]
- Index on collection that has the [maximum number of text indexes](https://www.mongodb.com/docs/manual/reference/limits/#mongodb-limit-Number-of-Indexes-per-Collection) [source]
For workloads which cannot tolerate performance decrease due to index builds, consider building indexes in a rolling fashion. [source]
- The Performance Advisor cannot suggest indexes for MongoDB databases configured to use the [ctime timestamp format](https://www.mongodb.com/docs/manual/reference/program/mongod/#cmdoption-mongod-timestampformat). As a workaround, set the timestamp format for such databases to either [iso8601-utc](https://www.mongodb.com/docs/manual/reference/program/mongod/#cmdoption-mongod-timestampformat) or [iso8601-local.](https://www.mongodb.com/docs/manual/reference/program/mongod/#cmdoption-mongod-timestampformat) [source]
[`createIndexes`](https://www.mongodb.com/docs/manual/reference/command/createindexes.md#mongodb-dbcommand-dbcmd.createIndexes) supports building one or more indexes on a collection. `createIndexes` uses a combination of memory and temporary files on disk to build indexes. In self-hosted MongoDB deployments, the default value of the [`maxIndexBuildMemoryUsageMegabytes`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.maxIndexBuildMemoryUsageMegabytes) parameter is 200 megabytes per `createIndexes` command. `createIndexes` shares the memory limit equally amon [source]
In self-hosted MongoDB deployments, you can change the memory limit for index builds by setting the `maxIndexBuildMemoryUsageMegabytes` parameter. Increasing this parameter is only necessary in rare cases. Such cases include running many simultaneous index builds with a single `createIndexes` command or indexing a data set larger than 500GB. You can also set `maxNumActiveUserIndexBuilds` to adjust how many index builds can run concurrently. [source]
On MongoDB Atlas, the default values of `maxIndexBuildMemoryUsageMegabytes` and `maxNumActiveUserIndexBuilds` vary by cluster tier. You cannot modify `maxIndexBuildMemoryUsageMegabytes` on MongoDB Atlas. [source]
The MCP Performance Advisor tool requires Atlas Project Read Only role or higher and an M10+ cluster, and MongoDB recommends running it with the --readonly flag; read-only mode and disabling tool categories restrict the server so an agent cannot call create-index or drop-index. [source]
Docs warn that every extra index slows writes and that the user should weigh read frequency against write cost; the docs also tell users to understand the specific schema warning before changing the schema. Applying every suggestion blindly is an antipattern. [source]
- You can't create indexes through the Performance Advisor if [Data Explorer](https://www.mongodb.com/docs/atlas/atlas-ui.md#std-label-atlas-ui) is disabled for your project. You can still view the Performance Advisor recommendations, but you must create those indexes from [`mongosh`.](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh) [source]
Limits the amount of memory that simultaneous [index builds](https://www.mongodb.com/docs/manual/core/index-creation.md#std-label-index-build-process) on one collection may consume for their duration. The specified amount of memory is shared between all indexes built using a single [`createIndexes`](https://www.mongodb.com/docs/manual/reference/command/createindexes.md#mongodb-dbcommand-dbcmd.createIndexes) command or its shell helper [`db.collection.createIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindexes.md#mongodb-method-db.collection.createIndexes) [source]
The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. [source]
Rolling indexes build indexes on the applicable nodes sequentially and may reduce the performance impact of an index build if your deployment's average CPU utilization exceeds (N-1)/N-10% where N is the number of CPU threads available to mongod of if the WiredTiger cache fill ratio regularly exceeds 90%. If your deployment does not meet this criteria, use the default index build. You can't create a rolling index on an M0 free cluster or M2/M5 shared cluster. [source]
While there is no hard limit on the number of collections in a single MongoDB Atlas cluster, the performance of a cluster might degrade if it serves a large number of collections and indexes. Larger collections have a greater impact on performance. [source]
| Option | Description | More Information | | --- | --- | --- | | Create unique index | Ensure that the indexed fields do not store duplicate values. You cannot perform a rolling build for a unique index. If you enable building indexes in a rolling fashion with the `unique` index option, Atlas rejects your configuration with an error message. | [Unique Indexes](https://www.mongodb.com/docs/manual/core/index-unique/) | | Index name | Specify a name for the index. | [Specify an Index Name](https://www.mongodb.com/docs/manual/core/indexes/create-index/specify-index-name.md#std-label-specify-index [source]
You can't create indexes through the Performance Advisor if Data Explorer is disabled for your project. You can still view the Performance Advisor recommendations, but you must create those indexes from mongosh. [source]
Starting in MongoDB 9.0, slow query logs include a `planRankerMethod` field that indicates which [query plan ranking mechanism](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plan-options) selected the winning plan: [source]
When performing slow query analysis, the MCP Server retrieves a sample of slow queries, capped at 50 queries. The sample includes up to 50 most recent slow queries that match any specified conditions in your prompt to ensure optimal performance and response times. [source]
If you run `$text` queries on a large dataset, a single-field text index may scan a large number of entries to return results, which can result in slow queries. [source]
[Unique](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#options-for-all-index-types) [index options](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#options) are incompatible with building indexes in a rolling fashion. If you specify `unique` in the Options pane, Atlas rejects your configuration with an error message. [source]
| Name | Type | Required | Description | | --- | --- | --- | --- | | --clusterName | string | true | Human-readable label that identifies the cluster. | | --envelope | envelope | false | Flag that indicates whether Application wraps the response in an envelope JSON object. Some API clients cannot access the HTTP response headers or status code. To remediate this, set envelope=true in the query. Endpoints that return a list of results use the results object as an envelope. Application adds the status parameter to the response body. | | --groupId | string | true | Unique 24-hexadecimal digit str [source]
The Query Profiler identifies slow queries and bottlenecks, and suggests index refinement and query restructuring to improve the performance of your database. It provides visibility into the slowest operations over up to seven days in the Atlas UI, making it easier to identify trends and outliers in query efficiency. We recommend that you use this data to pinpoint and troubleshoot poorly performing queries, reducing performance overhead. [source]
Without the index, the query scans the entire collection to return the matching documents. The query also has to scan the entirety of each document, potentially pulling them into memory. This results in an expensive and potentially slow query operation. [source]
Click Create Index on a slow query for instructions on how to create the recommended index. [source]
This task identifies queries with performance issues. If you already know which queries have performance issues, see [Explain Slow Queries](https://www.mongodb.com/docs/manual/tutorial/explain-slow-queries.md#std-label-manual-explain-slow-queries) to troubleshoot them. [source]
When a query shape has query knobs set through setQuerySettings or setParameter, slow query log entries include a queryKnobs document. The document shows the effective value of each knob and where the value comes from, with the same shape as the explain output. For details, see Query Knobs. [source]
| **MongoDB Version** | **Changes** | | --- | --- | | 6.1 | Slow operation log messages include [cache refresh time fields.](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-messages-cache-refresh-times) | | 6.2 | Slow operation log messages include a `queryFramework` field that indicates which query engine executed the query: `queryFramework: "classic"` indicates that the classic engine executed the query.; `queryFramework: "sbe"` indicates that the slot-based query execution engine executed the query. | | 6.3 | Slow operation log messages and [database profiler](ht [source]
| Text Index | MongoDB Search Index | | --- | --- | | `db.movies.aggregate([ { $match: { year: {$gte: 2000, $lte: 2010}, $text: { $search: "poet" }, genres : { $eq: "Comedy" } } }, { "$sort": { "year": 1 } }, { "$limit": 3 }, { "$project": { "_id": 0, "title": 1, "genres": 1, "plot": 1, "year": 1 }, [source]
Other $text restrictions: a query with $text cannot use hint(); text indexes do not improve sort performance; a multi-word phrase mixed with single terms matches only documents containing the full phrase; text indexes accept only binary comparison, so a collection with a non-simple collation needs collation {locale: 'simple'} on the text index; compound text indexes need equality conditions on key prefixes. [source]
Query settings have more functionality than index filters. Also, index filters aren't persistent and you cannot easily create index filters for all cluster nodes. To add query settings and explore examples, see [`setQuerySettings`.](https://www.mongodb.com/docs/manual/reference/command/setquerysettings.md#mongodb-dbcommand-dbcmd.setQuerySettings) [source]
The [Query Profiler](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler) displays slow-running operations and their key performance statistics. You can explore a sample of historical queries for up to the last seven days without additional cost or performance overhead. Before you enable the Query Profiler, see [Considerations.](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-atlas-considerations-profile-database) [source]
Collection scans indicate that the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries. [source]
A hexadecimal string that represents the hash of the [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) and is dependent only on the plan cache query shapes. `planCacheShapeHash` can help identify slow queries (including the query filter of write operations) with the same plan cache query shape. [source]
To see an example log message for a query that used the slot-based execution engine, see [Slow Operation.](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-json-examples-slow-op) [source]
- Operation Execution Time (`millis`): Query time in milliseconds. This is the primary indicator of a slow query. If this metric is consistently high, consider optimizing the query logic, batching results, or rethink how the data is being accessed. [source]
The Query Profiler is built from mongod slow-query log entries, not from the Database Profiler. Changing db.setProfilingLevel does not alter which slow queries the Query Profiler displays, and the UI works independently of profiling settings. [source]
A high-latency $lookup whose foreign-collection read is local to the shard can raise Namespace Insights latency and operation counts without producing a slow-query log for the foreign collection, so Namespace Insights and the Query Profiler can disagree. [source]
This setting strongly impacts query performance. Limit the maximum query length whenever possible. [source]
Specify `precision` and limit it when possible. Every digit increases storage overhead and has a high impact on searchable range and index generation. [source]
The Performance Advisor provides holistic schema recommendations for your cluster by sampling documents in your most active collections and collections with slow-running queries. [source]
| Name | Type | Required | Description | | --- | --- | --- | --- | | --duration | int | false | Length of time expressed during which the query finds suggested indexes among the managed namespaces in the cluster. This parameter expresses its value in milliseconds. If you don't specify the **since** parameter, the endpoint returns data covering the duration before the current time.; If you specify neither the **duration** nor **since** parameters, the endpoint returns data from the previous 24 hours. | | --envelope | envelope | false | Flag that indicates whether Application wraps the response [source]
| Field | Type | Description | | --- | --- | --- | | [`strMaxLength`](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#mongodb-parameter-param.strMaxLength) | integer | `substring` queries only. The maximum allowed length for a substring-indexed field. | | [`strMinQueryLength`](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#mongodb-parameter-param.strMinQueryLength) | integer | The minimum allowed prefix/suffix/substring length to query. | | [`strMaxQueryLength`](https://www.mongodb.com/docs/manual/ [source]
This limitation does not apply to the Performance Advisor UI in Atlas. [source]
WARNING: If you shard a collection that already has a MongoDB Search index, you might experience a brief period of incomplete search results while the index is building on a shard. Also, if you add a shard for an already sharded collection that contains a MongoDB Search index, your search queries against that collection might return incomplete results until the initial sync process completes on the added shards. To learn more, see [initial sync process.](https://www.mongodb.com/docs/search/performance/index-performance.md#std-label-troubleshoot-initial-sync) [source]
Text indexes cannot improve performance for sort operations. This restriction applies to both single-field and compound text indexes. [source]
In a small collection like the one used in the preceding example, there isn't a noticeable difference in performance between single-field and compound text indexes. However, in larger collections, increased index entry scans can noticeably hinder performance. For best performance, create text indexes that limit the number of index entries scanned to best fit your equality matches. [source]
Case-insensitive indexes do not improve performance for [`$regex`](https://www.mongodb.com/docs/manual/reference/operator/query/regex.md#mongodb-query-op.-regex) queries, as the `$regex` operator is not collation-aware and therefore cannot take advantage of such indexes. [source]
In 9.0 the classic engine reports expressionEvaluationPeakMemoryBytes in explain at executionStats verbosity or higher for $project, $addFields, $set, $unset, $replaceRoot, $replaceWith, $match, $bucketAuto, $graphLookup and $lookup. A failed operation logs 'Query exceeded the memory limit' with code 12932700. [source]
| Field | Type | Description | | --- | --- | --- | | `count` | string | The name of the collection or view to count. | | `query` | document | Optional. A query that selects which documents to count in the collection or view. | | `limit` | integer | Optional. The maximum number of matching documents to return. | | `skip` | integer | Optional. The number of matching documents to skip before returning results. | | `hint` | string or document | Optional. The index to use. Specify either the index name as a string or the index specification document. | | `readConcern` | document | Optional. Specifi [source]
These operations are also included in the logging of slow queries. See slowOpThresholdMs for more information about slow query logging. [source]
Some high-latency [`$lookup`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup) operations may not generate a slow query log for the foreign collection. This can occur because slow query logs correspond with operations that are reported in the [database profiler](https://www.mongodb.com/docs/manual/reference/database-profiler.md#std-label-profiler), whereas latency metrics increment only when a [collection lock](https://www.mongodb.com/docs/manual/faq/concurrency.md#std-label-faq-concurrency-locking) is acquired. [source]
If the `$lookup` query on a shard can perform a local read, the `$lookup` doesn't record a separate operation for querying the foreign collection. A local read refers to when the query on the foreign collection targets only the same shard where the current operation is being executed. As a result, the `$lookup` operation increases the `$collStats` latency metrics and operation counts, but does not generate a slow query log for the foreign collection. [source]
By default, Atlas dynamically adjusts your slow query threshold based on the execution time of operations across your cluster. However, you can opt out of this feature and instead use a fixed slow query threshold of 100 milliseconds. We don't recommend that you set the fixed slow query threshold lower than 100 milliseconds. [source]
Atlas clusters with MongoDB Search enabled don't support the Atlas-managed slow query operation threshold. [source]
For Free clusters (formerly known as `M0`), Atlas disables the Atlas-managed slow query operation threshold by default and you can't enable it. [source]
For a [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instance, the command enables, disables, or configures the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler). The profiler captures and records data on the performance of write operations, cursors, and database commands on a running [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instance. If the profiler is disabled, the command configures how slow operations [source]
| Field | Type | Description | | --- | --- | --- | | `profile` | int | Configures the profiler level. The following profiler levels are available: `0`The profiler is off and does not collect any data. This is the default profiler level.`1`The profiler collects data for operations that exceed the `slowms` threshold or match a specified [filter.](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-options-filter)When a filter is set:The `slowms` and `sampleRate` options are not used for profiling.; The profiler only captures operations that [source]
In rare circumstances, a write can fail due to cache pressure. When this happens MongoDB issues a `TemporarilyUnavailable` error and increments the `temporarilyUnavailableErrors` counter in two places: the slow query log and the [Full Time Diagnostic Data Capture (FTDC).](https://www.mongodb.com/docs/manual/administration/full-time-diagnostic-data-capture.md#std-label-ftdc-stub) [source]
deprecated index filters. (Starting in MongoDB 8.0, use the new query settings for the new query shape.) [source]
| Field | Type | Description | | --- | --- | --- | | `createIndexes` | string | The collection for which to create indexes. | | `indexes` | array | Specifies the indexes to create. Each document in the array specifies a separate index. | | `writeConcern` | document | Optional. A document expressing the [write concern](https://www.mongodb.com/docs/manual/reference/write-concern.md). Omit to use the default write concern. | | `commitQuorum` | integer or string | Optional. The minimum number of data-bearing replica set members (i.e. commit quorum), including the primary, that must report a succes [source]
To optimize your query performance, [review the best practices for query performance](https://www.mongodb.com/docs/atlas/analyze-slow-queries.md#std-label-query-best-practices). You can also [analyze slow queries](https://www.mongodb.com/docs/atlas/analyze-slow-queries.md#std-label-analyze-slow-queries) and troubleshoot slow operations executed on your clusters. [source]
MongoDB Compass displays the COLLSCAN query stage to indicate that a collection scan was used for this query. Collection scans indicate that the mongod had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries. [source]
[ { "x.a": { $gt: 85 } }, { "x.b": { $gt: 80 } } ]` However, you can specify compound conditions on the same identifier in a single filter document, such as in the following examples: `// Example 1 [ { $or: [{"x.a": {$gt: 85}}, {"x.b": {$gt: 80}}] } ] // Example 2 [ { $and: [{"x.a": {$gt: 85}}, {"x.b": {$gt: 80}}] } ] // Example 3 [ { "x.a": { $gt: 85 }, "x.b": { $gt: 80 } } ]` For examples, see [Array Update Operations with `arrayFilters`.](https://www.mongodb.com/docs/manual/reference/command/findandmodify.md#std-label-findAndModify-command-arrayFilters) `arrayFilters` is not available for u [source]
| Field | Type | Literal or Normalized | Description | | --- | --- | --- | --- | | `key` | Document | Literal | Contains the query shape and additional query attributes that group a set of queries together | | `key.queryShape` | Document | Literal | Contains attributes used to group similar queries together. For more information, see [Query Shape.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-queryShape) | | `key.client` | Document | Literal | Describes client information associated with the key | | `key.client.application` | Document | [source]
Learn more about slow queries and get suggestions to improve database performance. [source]
| Field | Description | | --- | --- | | `version` | A number that indicates the query engine used to complete the query. `1` indicates that the classic engine was used.; `2` indicates that the [slot-based query execution engine](https://www.mongodb.com/docs/manual/reference/sbe.md#std-label-sbe-landing) was used. | | `createdFromQuery` | A document that contains the specific query that resulted in this cache entry. For example: `{ "query" : <document>, "sort" : <document>, "projection" : <document> }` | | `isActive` | A boolean that indicates whether the entry is active or inactive. If active, [source]
These operations are also included in the logging of slow queries. See [`slowOpThresholdMs`](https://www.mongodb.com/docs/manual/reference/configuration-options.md#mongodb-setting-operationProfiling.slowOpThresholdMs) for more information about slow query logging. [source]
MongoDB automatically enables block processing for eligible time series queries. You cannot manually specify whether a query uses block processing. To see if your time series query uses block processing, see [`stages`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.winningPlan.slotBasedPlan.stages) in the explain plan output. [source]
The [`$currentOp`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/currentop.md#mongodb-pipeline-pipe.-currentOp) aggregation stage provides information on all operations currently running on MongoDB. If your application is experiencing performance issues, you can build an aggregation pipeline around this stage to monitor for slow queries and similar issues. [source]
| Profiler | Description | | --- | --- | | [Atlas Query Profiler](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler) | Provides a scatterplot chart to Atlas customers, making it easy to identify slow queries and performance outliers. | | [Database Profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) | Stores query performance information in a collection, allowing you to query MongoDB for queries with specific performance issues. | [source]
The totalOplogSlotDurationMicros in the slow query log message shows the time between a write operation getting a commit timestamp to commit the storage engine writes and actually committing. mongod supports parallel writes. However, it commits write operations with commit timestamps in any order. [source]
The slow query output includes a queues document that contains information about the operation's queues. Each queue in the queues field contains a totalTimeQueuedMicros field that contains the total cumulative time in microseconds that the operation spent in the corresponding queue. [source]
The queryShapeHash field for a query shape is also included in the slow query log when available. [source]
Slow query log messages contain new metrics if the query execution writes temporary files to disk. These metrics are prefixed by the query execution stage that caused the query to exceed the memory limit. For example, sortSpills indicates the number of times that the sort stage of query execution wrote temporary files to disk. [source]
MongoDB 8.3 also introduces slow in-progress query log entries. Queries trigger a slow-in progress log during execution. These logs contain less information than standard slow query logs. MongoDB logs queries at most once if the query's duration surpasses the operationProfiling.slowOpInProgressThresholdMs. [source]
When an operation specifies a time limit for a query, slow query logs contain a deadline field. deadline indicates the time the operation must complete by, which is equal to the time at the start of the operation plus the value of the query timeout. [source]
To determine if a merge operation or a shard issue is causing a slow query, compare the workingMillis and remoteOpWaitMillis time fields in the log. workingMillis is the total time the query took to complete. Specifically: [source]
When using Queryable Encryption, CRUD operations against encrypted collections are omitted from the slow query log. For details, see Queryable Encryption redaction. [source]
Starting in MongoDB 7.0, log messages for slow queries also include the catalogCacheIndexLookupDurationMillis field that indicates the time that the operation spent fetching information from the index cache. This release also renames the shardVersionRefreshMillis field to placementVersionRefreshDurationMillis. [source]
If MongoDB cannot use an index or indexes to obtain the sort order, the results include a `SORT` stage indicating an in-memory sort operation. If the explain plan does not contain an explicit `SORT` stage, then MongoDB used an index to obtain the sort order. [source]
When using [Queryable Encryption](https://www.mongodb.com/docs/manual/core/queryable-encryption.md#std-label-qe-manual-feature-qe), CRUD operations against encrypted collections are omitted from the slow query log. For details, see [Queryable Encryption redaction.](https://www.mongodb.com/docs/manual/core/queryable-encryption/reference/limitations.md#std-label-qe-redaction) [source]
Assume a [slow query](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) is run on `myView`. The following example log message contains a `resolvedViews` field for `myView`: [source]
The Query Profiler diagnoses and monitors performance issues. This monitoring can expose slow-running queries and their key performance statistics in the Atlas UI. [source]
To opt out of the Atlas-managed slow operation threshold and use a fixed slow query threshold of 100 milliseconds instead, use the Atlas Administration API. See [Disable Managed Slow Operation Threshold](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/operation/operation-disablegroupmanagedslowms). For Free clusters (formerly known as `M0`) and Flex clusters, Atlas disables the Atlas-managed slow query operation threshold by default and you can't enable it. [source]
Some high-latency $lookup operations don't generate a slow query log for the foreign collection. This can occur because slow query logs correspond with operations that are reported in the Query Profiler, whereas latency metrics increment only when a collection lock is acquired. [source]
| Field | Type | Description | | --- | --- | --- | | [setIndexCommitQuorum](https://www.mongodb.com/docs/manual/reference/command/setindexcommitquorum.md#std-label-setIndexCommitQuorum-cmd-coll) | `string` | The name of the collection for which the indexes are being built. | | [indexNames](https://www.mongodb.com/docs/manual/reference/command/setindexcommitquorum.md#std-label-setIndexCommitQuorum-cmd-indexNames) | `array` | An array of in-progress index builds to modify. Each element of the array must be the *name* of the index. The indexes specified to `indexNames` must be the entire set of i [source]
COMPASS-7062 (open, unassigned, Minor, reported against Compass 1.39.0 and MongoDB 6.0.7): for an aggregation with $match plus $lookup running on the classic (non-SBE) engine, the Compass explain summary counts only the documents and keys examined by the $match. It hides $lookup work such as collection scans, so a slow pipeline can look efficient. With SBE the summary is accurate. Check Raw Output when a pipeline contains $lookup. [source]
- [atlas api performanceAdvisor enableManagedSlowMs](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-enablemanagedslowms.md#std-label-atlas-api-performanceAdvisor-enableManagedSlowMs) - Enables MongoDB Cloud to use its slow operation threshold for the specified project. [source]
| Status | Description | | --- | --- | | `BUILDING` | The following scenarios can cause an index to be in the `BUILDING` state: MongoDB is building the index or re-building the index after an edit.; MongoDB Search cannot keep up with indexing changes to the collection. In this case, Atlas rebuilds the index in the background. When the index is in the `BUILDING` state: For a new index, MongoDB Search cannot use the index for queries until the index build is complete.; For an existing index, MongoDB Search uses the old index definition for queries until the index rebuild is complete. An index in [source]
A case-sensitive $regex can use an index range scan only when the pattern is an anchored simple prefix (starts with ^ or \A followed by simple characters); the scan stops once the prefix no longer matches. Unanchored patterns and patterns with leading wildcards cannot narrow the index range. [source]
- [atlas api performanceAdvisor disableManagedSlowMs](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-disablemanagedslowms.md#std-label-atlas-api-performanceAdvisor-disableManagedSlowMs) - Disables the slow operation threshold that MongoDB Cloud calculated for the specified project. [source]
Starting in MongoDB 8.0, the existing `queryHash` field is duplicated in a new field named `planCacheShapeHash`. If you're using an earlier MongoDB version, you'll only see the `queryHash` field. Future MongoDB versions will remove the deprecated `queryHash` field, and you'll need to use the `planCacheShapeHash` field instead. [source]
| `$exists` Query | Using a Sparse Index | Using a Non-Sparse Index | | --- | --- | --- | | `{ $exists: true }` | Most efficient. MongoDB can make an exact match and does not require a `FETCH`. | More efficient than queries without an index, but still requires a `FETCH`. | | `{ $exists: false }` | Cannot use the index and requires a `COLLSCAN`. | Requires a `FETCH`. | [source]
- Use the [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match), [`$limit`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit.md#mongodb-pipeline-pipe.-limit), and [`$skip`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/skip.md#mongodb-pipeline-pipe.-skip) stages to restrict the documents that enter the pipeline. [source]
Slow operations are logged based on `workingMillis`, which is the amount of time that MongoDB spends working on that operation. This means that factors such as waiting for locks and flow control do not affect whether an operation exceeds the slow operation threshold. [source]
When [`logLevel`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.logLevel) is set to `0`, MongoDB records *slow* operations to the diagnostic log at a rate determined by [`slowOpSampleRate`.](https://www.mongodb.com/docs/manual/reference/configuration-options.md#mongodb-setting-operationProfiling.slowOpSampleRate) [source]
At higher `logLevel` settings, all operations appear in the diagnostic log regardless of their latency with the following exception: the logging of slow oplog entry messages by the secondaries. The secondaries log only the slow oplog entries; increasing the `logLevel` does not log all oplog entries. [source]
The slow operation time threshold for an in-progress query, in milliseconds. MongoDB logs operations that run for longer than this threshold as slow in-progess queries. MongoDB logs a query as a slow in-progress query as soon as the query operation crosses the time threshold. [source]
The fraction of *slow* operations that should be profiled or logged. [`--slowOpSampleRate`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--slowOpSampleRate) accepts values between 0 and 1, inclusive. [source]
[`--slowOpSampleRate`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--slowOpSampleRate) does not affect the slow oplog entry logging by the secondary members of a replica set. Secondary members log all oplog entries that take longer than the slow operation threshold regardless of the [`--slowOpSampleRate`.](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--slowOpSampleRate) [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.filter` | Document | Normalized | | `key.queryShape.sort` | Document | Literal | | `key.queryShape.projection` | Document | Normalized | | `key.queryShape.skip` | Integer | Normalized | | `key.queryShape.limit` | Integer | Normalized | | `key.queryShape.singleBatch` | Boolean | Literal | | `key.queryShape.max` | Document | Normalized | | `key.queryShape.min` | Document | Normalized | | `key.queryShape.returnKey` | Boolean | Literal | | `key.queryShape.showRecordId` | Boolean | Literal | | `key.queryShape.tailable` | [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.collation` | Document | Normalized | | `key.queryShape.query` | Document | Normalized | | `key.queryShape.limit` | Integer | Normalized | | `key.queryShape.skip` | Integer | Normalized | [source]
`key.queryShape.limit` reflects the `limit` field of the delete statement, where `0` deletes all matching documents and `1` deletes a single document. It does not cap the number of query stats entries. [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.q` | Document | Normalized | | `key.queryShape.limit` | Integer | Literal | | `key.queryShape.let` | Document | Normalized | | `key.queryShape.collation` | Document | Literal | [source]
With [slow operation logging](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) enabled, the following returns only the slow operations that took above 2000 milliseconds:, for further analysis: [source]
- Response Length: Unusually large response lengths indicate that queries are returning more data than necessary. Consider using [projections](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#std-label-projections) to limit the fields returned. [source]
- [atlas performanceAdvisor slowOperationThreshold](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceadvisor-slowoperationthreshold.md#std-label-atlas-performanceAdvisor-slowOperationThreshold) - Enable or disable management of the slow operation threshold for your cluster. [source]
- [atlas api performanceAdvisor getManagedSlowMs](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-getmanagedslowms.md#std-label-atlas-api-performanceAdvisor-getManagedSlowMs) - Get whether the Managed Slow MS feature is enabled. [source]
| Field | Type | Description | | --- | --- | --- | | `distanceField` | string | The output field that contains the calculated distance. To specify a field within an embedded document, use [dot notation.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-dot-notation) This field is required for queries on timeseries collections. Starting in MongoDB 8.1, `distanceField` is optional for queries on non-timeseries collections. | | `distanceMultiplier` | number | Optional. The factor to multiply all distances returned by the query. For example, use the `distanceMultiplier` to conver [source]
Starting in MongoDB 9.0, if an indexed field becomes a multikey field while a query that references the field is running, the query might fail with a QueryKilledError. An indexed field becomes multikey when you insert or update a document so the field contains an array value. [source]
| Field | Description | | --- | --- | | [into](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#std-label-merge-into) | The output collection. Specify either: The collection name as a string to output to a collection in the same database where the aggregation is run. For example:`into: "myOutput"`; The database and collection name in a document to output to a collection in the specified database. For example:`into: { db:"myDB", coll:"myOutput" }` If the output collection does not exist, `$merge` creates the collection: For a [replica set](https://www.mongodb.com/docs [source]
You can use the metrics in the slow operation log to identify where an operation spends time in its lifecycle, which helps identify possible performance improvements. [source]
This endpoint only returns query shapes with REJECTED status. If the specified query shape hash does not correspond to a rejected query shape, a 404 Not Found error is returned. [source]
To help identify slow queries with the same [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape), each plan cache query shape is associated with a query hash. The plan cache query shape hash is a hexadecimal string that represents a hash of the query shape and is dependent only on the query shape. [source]
- [profiler log messages](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) and [diagnostic log messages (i.e. mongod/mongos log messages)](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-messages-ref) when logging slow queries. [source]
Atlas can send you a Query Shape Report email that summarizes inefficient query shapes for a cluster in your project. To help you identify and investigate slow queries, the report links directly to the Query Shape Insights page. [source]
| Field | Type | Description | | --- | --- | --- | | `getMore` | long | The cursor identifier. | | `collection` | string | The name of the collection over which the cursor is operating. | | `batchSize` | positive integer | Optional. The maximum number of documents returned for each batch. If `batchSize` is not set, `getMore` returns up to 16 mebibytes (MiB) for each batch. If `batchSize` is set, `getMore` returns the lesser of `batchSize` documents or 16 MiB. | | `maxTimeMS` | non-negative integer | Optional. Specifies how long the server waits for new documents matching a [tailable cursor](ht [source]
For a [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instance, the command only configures how operations get written to the diagnostic log. You cannot enable the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) on a [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instance because `mongos` does not have any collections that the profiler can write to. [source]
With [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instances, the method sets the `slowms`, `sampleRate` and `filter` configuration settings, which configure how operations get written to the diagnostic log. You cannot enable the [Database Profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md) on a [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instance because `mongos` does not have any collections that the profiler can write to. The `profile` level m [source]
- [atlas api performanceAdvisor listPerformanceAdvisorNamespaces](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listperformanceadvisornamespaces.md#std-label-atlas-api-performanceAdvisor-listPerformanceAdvisorNamespaces) - Returns up to 20 namespaces for collections experiencing slow queries on the specified host. [source]
Use the following methods to identify slow queries that occur on your deployment. [source]
Get log lines for slow queries for a specified host [source]
You can return log lines for slow queries that the Query Profiler identifies from the Atlas Administration API with [Return Slow Queries.](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/operation/operation-listslowqueries) [source]
If available, the [Project Overview](https://www.mongodb.com/docs/atlas/project-overview.md#std-label-project-overview) page includes a View Query Insights section with alerts and high-level metrics for slow queries and sampled P95 latency. This section also includes a shortcut to the Query Insights tab. [source]
Collection scans indicate that the mongod had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries. [source]
The profiler does not capture slow oplog entries. [source]
You cannot run $queryStats on a specific collection. For complete examples, see Examples. [source]
Statistics for the $queryStats stage are tracked in a virtual collection that is stored in-memory. The memory limit for the virtual collection is 1% of the system's total memory. [source]
Pattern names describe plans at a coarse level. They omit limit and skip stages, and they don't distinguish whether a stage applies a filter or which type of projection a plan uses. To determine those details, combine a pattern counter with the nodes counters and the query shape for the entry. [source]
You cannot run `$queryStats` on a specific collection. For complete examples, see [Examples.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-examples) [source]
- [atlas performanceAdvisor slowQueryLogs](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-performanceadvisor-slowquerylogs.md#std-label-atlas-performanceAdvisor-slowQueryLogs) - Get log lines for slow queries for a specified host [source]
Returns up to 20 namespaces for collections experiencing slow queries on the specified host. [source]
| Field | Type | Description | | --- | --- | --- | | `query` | document | Optional. The query predicate of the plan cache query shape. Only the structure of the predicate, including the field names, are significant to the shape; the values in the query predicate are insignificant. | | `projection` | document | Optional. The projection associated with the plan cache query shape. | | `sort` | document | Optional. The sort associated with the plan cache query shape. | | `comment` | any | Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records [source]
Retrieve namespaces for collections experiencing slow queries [source]
| Notification Option | Description | | --- | --- | | Atlas Project | Sends the alert by email or text message to users with specific roles in the Project. Atlas Project is the default alert recipient. You can configure the roles the alert is sent to and how it's delivered. You can't add a second Atlas Project as the recipient. If the [`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) role is assigned to a human project member, Atlas delivers alerts to that member's configured email address or SMS target. If the [`Project Owner`](https: [source]
This task uses [`$currentOp`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/currentop.md#mongodb-pipeline-pipe.-currentOp) to identify slow queries currently running on your application. To find all slow queries within a specified period, consider using a profiler. [source]
planCacheShapeHash to help identify slow queries with the same plan cache query shape. [source]
planCacheKey to provide more insight into the query plan cache for slow queries. [source]
The following table lists the changes to logging slow queries. [source]
Slow operation log messages and database profiler entries include a cpuNanos field that specifies the total CPU time spent by a query operation in nanoseconds. The cpuNanos field is only available on Linux systems. [source]
Starting in MongoDB 5.0, log messages for slow queries on views include a resolvedViews field that contains the view details: [source]
Starting in MongoDB 5.0, log messages for slow queries include a system.profile.authorization section. These metrics help determine if a request is delayed because of contention for the user authorization cache. [source]
Starting in MongoDB 6.1, log messages for slow queries include the following cache refresh time fields: [source]
The [profiler entries](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) and the [diagnostic log messages (i.e. mongod/mongos logmessages)](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) for read/write operations include: [source]
- `planCacheShapeHash` to help identify slow queries with the same [plan cache query shape.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) [source]
- `planCacheKey` to provide more insight into the [query plan cache](https://www.mongodb.com/docs/manual/core/query-plans.md) for slow queries. [source]
Starting in MongoDB 5.0, [log messages for slow queries](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) on [views](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) include a `resolvedViews` field that contains the view details: [source]
The CRUD operations below are omitted from the [slow operations](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) query log and the [Database Profiler](https://www.mongodb.com/docs/manual/reference/database-profiler.md#std-label-profiler) `system.profile` collection when you run them on an encrypted collection. [source]
Atlas collects and displays statistics from any of your [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instances. The Query Profiler stores your `mongod` query [log data](https://www.mongodb.com/docs/atlas/mongodb-logs.md#std-label-mongodb-logs), which can be accessed directly through the Query Profiler or through the Atlas CLI, and use it to identify slow queries. Atlas displays this data on the Query Insights page, in the Query Profiler tab of an instance. [source]
The Atlas Query Profiler shows operations across your entire cluster by default. You can filter the Query Profiler view to only operations coming from specified hosts with the Filter by Hosts dropdown, which allows you to view data related to one or more specific hosts or to view primary or secondary host groups. The Query Insights option in the Cluster sidebar displays information to reveal slow database operations over a set time frame, such as the following: [source]
The following section describes how to find slow queries using the Query Profiler. [source]
The Query Profiler uses a scatterplot chart and a table directly below the chart to help you find slow operations. [source]
Find Slow Queries with the Query Profiler [source]
Scroll down to the table below the chart to view a list of slow queries by collection. Click a row in the table for more details. [source]

Comparisons and alternatives

Starting in MongoDB 9.0, you can use the `queryKnobs` setting to override internal server parameters for a single [query shape](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) instead of the whole instance. Set the `queryKnobs` field for targeted queries to mitigate the impact of one regressed shape without changing behavior for other workloads on the same deployment. [source]
If the slow query log contains consecutive [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) stages in the aggregation pipeline, the two stages can [coalesce](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization/#-match----match-coalescence) into the first [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) stage and result in a single [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe [source]
If the slow query log contains consecutive $match stages in the aggregation pipeline, the two stages can coalesce into the first $match stage and result in a single $match stage. As a result, the query shape in the Performance Advisor might differ from the actual query you ran. [source]
Index field order is chosen by operator type: equality, then $in array, range, $type, $exists, other operators, then sort last; fields are generally ranked by cardinality. This differs from the ESR rule (equality, sort, range) that manual design uses, so check suggested order against sort-bearing queries. [source]
Scope limits: not supported for databases using the ctime timestamp format or for time series collections; during extreme log spikes Atlas may stop collecting logs; consecutive $match stages can coalesce so the displayed shape can differ from the query as written. Suggested indexes are created cluster-wide even when viewing one shard. [source]
Indexes improve read performance, but a large number of indexes can negatively impact write performance since indexes must be updated during writes. If your collection already has several indexes, consider this tradeoff of read and write performance when deciding whether to create new indexes. Examine whether a query for such a collection can be modified to take advantage of existing indexes, as well as whether a query occurs often enough to justify the cost of a new index. [source]
If you create this index, you remove the need for the database engine to scan the entire collection to find documents that match the query, which improves performance. Queries with the shape shown in the [example](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#std-label-taxi-example-query) return results in 50 milliseconds or less on the indexed collection, as opposed to several seconds on the unindexed collection. [source]
The Query Profiler differs from the [Database Profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler/). The Query Profiler identifies specific inefficient queries based on entries from your `mongod` logs. The Database Profiler returns detailed information about commands executed on the `mongod` based on the specified [profiling level](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler/#profiling-levels). Changing the profiling level doesn't impact the slow queries displayed in the Query Profiler. [source]
| Field | Field Type | Necessity | Description | | --- | --- | --- | --- | | `setQuerySettings` | document or string | Required | You can provide either: The same fields as those in a [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find), [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct), or [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) command, and a `$db` field with the database associated with the original command [source]
| Field | Type | Description | | --- | --- | --- | | `planCacheSetFilter` | string | The name of the collection for the index filter. | | `query` | document | The query predicate for the index filter. Only the predicate structure, including the field names, is used in the index filter. The field values in the query predicate are not used. Therefore, the query predicate in an index filter is used by similar queries that differ only in the field values. | | `sort` | document | Optional. The sort for the index filter. | | `projection` | document | Optional. The projection for the index filter. | [source]
| Option | Description | | --- | --- | | allowDiskUse | Whether or not pipelines that require more than 100 megabytes of memory to execute write to temporary files on disk. For details, see [`cursor.allowDiskUse()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.allowdiskuse.md#mongodb-method-cursor.allowDiskUse) | | allowPartialResults | For queries against a sharded collection, allows the command (or subsequent getMore commands) to return partial results, rather than an error, if one or more queried shards are unavailable. | | awaitData | If the cursor is a a tailable-await cur [source]
- [MongoDB Search](https://www.mongodb.com/docs/atlas/atlas-search/) provides improved performance and functionality compared to on-premises text search. [source]
Starting in MongoDB 8.0, use query settings instead of adding [index filters](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters). Index filters are deprecated starting in MongoDB 8.0. [source]
- Keys Examined (`keysExamined`): Number of index keys scanned for the query. If this metric is `0` for a query that includes filter conditions, it's highly likely there is no index and MongoDB scanned the entire collection. This is a primary cause of slowness. If Docs Examined is very high and Keys Examined is `0` or very low compared to Docs Examined, you're likely scanning the collection or a very unselective index. To learn more, see [Indexes.](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) [source]
You can use hidden indexes to evaluate the potential impact of dropping an index without actually dropping it. If the impact is negative, you can unhide the index instead of having to recreate a dropped index. Hidden indexes are fully maintained and can be used immediately once unhidden. [source]
By hiding an index from the planner, you can evaluate the potential impact of dropping an index without actually dropping the index. If the impact is negative, you can unhide the index instead of having to recreate a dropped index. And because indexes are fully maintained while hidden, the indexes are immediately available for use once unhidden. [source]
| Condition | Recommended Alert Threshold: Low Priority | Recommended Alert Threshold: High Priority | Key Insights | | --- | --- | --- | --- | | Oplog Window | < 24h for 5 minutes | < 1h for 10 minutes | Monitor the replication oplog window, together with replication headroom, to determine whether the secondary may soon require a full resync. The replication oplog window often helps to determine in advance the resilience of secondaries to planned and unplanned outages. | | [Election](https://www.mongodb.com/docs/manual/core/replica-set-elections/) events | > 3 for an hour, notify after 5 minu [source]
If, however, the impact is negative, the user can unhide the index instead of having to recreate a dropped index. And because indexes are fully maintained while hidden, the indexes are immediately available for use once unhidden. [source]
A compound text index on the `department` and `description` fields limits the index keys scanned to only documents within the specified `department`. The compound text index provides improved performance compared to a single-field text index on the `description` field. [source]
MongoDB's 9.0 performance claims versus 8.0: up to 35% faster find-one, 30% faster update-one, 20% higher transactional throughput, nearly 2x throughput on large deployments. The vendor says these come from internal testing and vary by workload. Percona could not find methodology or raw data, and press coverage reports no benchmark configuration. [source]
8.2.0 known issues: up to 15% change-stream performance degradation versus 8.0, and a $group distinct-scan-on-sparse-index bug that hides null/missing results (SERVER-110423) fixed in 8.2.2. [source]
| State | Description | | --- | --- | | [Missing](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-cache-entry-missing) | No entry for this shape exists in the cache. For a query, if the cache entry state for a plan cache query shape is [Missing:](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-cache-entry-missing) Candidate plans are evaluated and a winning plan is selected.; The cache creates an entry for the plan cache query shape in state [Inactive](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-cache-entry-inactive) with a value that q [source]
Unlike `planCacheShapeHash`, `planCacheKey` is a function of both the query shape and the currently available indexes for the shape. That is, if indexes that can support the query shape are added/dropped, the `planCacheKey` value may change whereas the `planCacheShapeHash` value would not change. [source]
Starting in MongoDB 8.0, add query settings for query shapes instead of index filters for collections. Index filters are deprecated starting in MongoDB 8.0. Query settings have more functionality than index filters, and index filters aren't persistent after cluster shutdown. [source]
| Field | Type | Description | | --- | --- | --- | | dropIndexes | String | The name of the collection whose indexes to drop. | | index | string or document or array of strings | The index or indexes to drop. To drop all indexes except the `_id` index and the last remaining shard key index from the collection if one exists, specify `"*"`.; To drop a single index, specify either the index name, the index specification document (unless the index is a [text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text.md#std-label-index-type-text) index), or an array of the index name. [source]
| Field | Type | Description | | --- | --- | --- | | `find` | string | The name of the collection or [view](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) to query. | | `filter` | document | Optional. The query predicate. If unspecified, all documents in the collection match the predicate. | | [sort](https://www.mongodb.com/docs/manual/reference/command/find.md#std-label-find-cmd-sort) | document | Optional. The sort specification for the ordering of the results. | | `projection` | document | Optional. The [projection specification](https://www.mongodb.com/docs [source]
Unlike [`planCacheShapeHash`](https://www.mongodb.com/docs/manual/reference/database-profiler.md#mongodb-data-system.profile.planCacheShapeHash), [`planCacheKey`](https://www.mongodb.com/docs/manual/reference/database-profiler.md#mongodb-data-system.profile.planCacheKey) is a function of both the plan cache query shape and the currently available indexes for that shape. Specifically, if indexes that can support the query shape are added or dropped, the `planCacheKey` value may change but `planCacheShapeHash` would not. [source]
To drop a [text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text.md#std-label-index-type-text) index, specify the index name instead of the index specification document. [source]
The second compound index, `{ rated: 1, year: 1 }`, is therefore the more efficient index for supporting the example query, as the MongoDB server only needs to scan `235` [`index keys`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined) to find all matching documents using this index, compared to `246` when using the compound index `{ year: 1, rated: 1 }`. [source]
The same study recommends validating LLM-recommended indexes with performance tests before production, treating the LLM as complementary to cost-based advisors rather than a replacement, and distilling LLM heuristics into deterministic rules; with more queries in the prompt the LLM 'distracts' from the costliest query (about 50% of runs matched DTA on it alone vs below 5% with four queries). [source]
For example, you can override the [`notablescan`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.notablescan) server parameter for a single query shape instead of the whole instance by setting `queryKnobs` to `{ noTableScan: true }`. [source]
Unlike [`setParameter`](https://www.mongodb.com/docs/manual/reference/command/setparameter.md#mongodb-dbcommand-dbcmd.setParameter), which applies per process and can differ between nodes, MongoDB applies the same `queryKnobs` values across the whole cluster for the matching query shape. [source]
- The Atlas [Query Profiler](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler) records operations that Atlas considers slow when compared to average execution time for all operations on your cluster. [source]
The Atlas Query Profiler records operations that Atlas considers slow when compared to average execution time for all operations on your cluster. [source]

Facts and statements

The Performance Advisor also shows each executed sample query that matches the query shape, with specific metrics for that query. [source]
Log analysis rate limits apply only to the Performance Advisor UI, the Query Insights UI, and the Access Tracking UI. [Downloadable log files](https://www.mongodb.com/docs/api/doc/atlas-admin-api-v2/operation/operation-downloadgroupclusterlog) are always complete. [source]
The Performance Advisor monitors queries that take longer than `100` milliseconds to execute and groups these queries into common [query shapes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape). The Performance Advisor calculates the inefficiency of each query shape by considering the following aggregated metrics from queries which match the shape: [source]
For each suggested index, the Performance Advisor shows the most commonly executed query shapes that the index would improve. For each query shape, the Performance Advisor displays the following metrics: [source]
The type of query operation in the query shape affects the order of the fields used to construct the index. In general, the Performance Advisor ranks fields by their [cardinality.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-cardinality) [source]
The type of query operation in the query shape affects the order of the fields used to construct the index. In general, the Performance Advisor ranks fields by their cardinality. [source]
| Metric | Description | | --- | --- | | Execution Count | Number of queries executed per hour which match the query shape. | | Average Execution Time | Average execution time in milliseconds for queries which match the query shape. | | Average Query Targeting | Average number of documents read for every document returned by matching queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see [Query Targeting.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-query-targeting) | | Average Docs Scanned | Aver [source]
In the Database Monitoring Tools section, toggle Performance Advisor and Profiler to Off. [source]
- The query scanned many documents to find the results, which could impact network latency and application performance. [source]
The Query Profiler is available only on M10+ clusters. It is enabled by default. Disabling it requires Project Owner (or Organization Owner); viewing requires Project Read Only or Project Observability Viewer. It is toggled together with Performance Advisor under Project Settings > Database Monitoring Tools. [source]
The Performance Advisor fetches index recommendations from the past 24 hours. [source]
- If the Performance Advisor has no recommendations, to look up index recommendations for up to the past 5 days, click Explore Recommendations in the Create Indexes section. [source]
By default, the Performance Advisor shows index recommendations for all collections in your cluster. To narrow the recommendations down to a specific collection, select one from the Collection dropdown menu. [source]
Including `$queryStats` output in the logs provides more information on specific `$queryStats` operations, but can negatively impact cluster performance as log updates take more time and resources. [source]
Log analysis rate limits apply only to the Performance Advisor UI, the Query Insights UI, and the Access Tracking UI. Downloadable log files are always complete. [source]
- **Performance:** You can use built-in tools like Query Insights and Performance Advisor to optimize performance, improve database operations, and manage costs. [source]
2. Toggle the button next to Performance Advisor and Profiler. [source]
- Text index on collection that has the [maximum number of text indexes](https://www.mongodb.com/docs/manual/core/index-text/#one-text-index-per-collection) [source]
3. Click Performance Advisor. [source]
The Performance Advisor doesn't provide performance suggestions for time series collections. [source]
- Optimize your indexes and remove unused or inefficient indexes. Too many indexes can negatively impact write performance. [source]
- Consider the suggested indexes from the [Performance Advisor](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-performance-advisor) with the highest Impact scores and lowest Average Query Targeting scores. [source]
- Perform [rolling index builds](https://www.mongodb.com/docs/manual/tutorial/build-indexes-on-replica-sets/#rolling-index-builds-on-replica-sets) to reduce the performance impact of building indexes on [replica sets](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-replica-set) and [sharded clusters.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-sharded-cluster) [source]
- [Drop unused, redundant, and hidden indexes](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#std-label-pa-drop-index) to improve write performance and free storage space. [source]
This example creates a new index and modifies that index to make it [unique.](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique) [source]
1. Create a `siteAnalytics` collection with an index on the `url` field [source]
This temporary index lets you safely drop the original `{ "url": 1 }` index without impacting performance. [source]
3. Drop the original index [source]
4. Recreate the `{ "url": 1 }` index with the `unique` property [source]
The `url_1` index is recreated and you can drop the temporary index without impacting performance. Queries on the `url` field can use the new unique index. [source]
5. Drop the temporary index [source]
6. Confirm that the index was updated [source]
The command returns these indexes, indicating that the `url_1` index is now unique: [source]
Consider designating a maintenance window during which applications stop or reduce write operations against the collection. Start the index build during this maintenance window. [source]
Suggested indexes are ranked by 'Impact', defined as total wasted bytes read, computed from per-shape execution time, documents scanned, documents returned and average object size. [source]
Drop-index recommendations cover unused indexes (no query support for 7+ days since creation or server restart; only the 20 most active collections inspected), redundant indexes (prefix of another index in the same collection), and hidden indexes (always recommended for drop). Docs advise hiding an index before dropping it to test impact. [source]
Suggestions are based on a sample of recent log lines, not full workload history; a community reply says roughly the last 200k log lines. A suggested index can therefore show zero use in $indexStats if the query stopped running or if a mongod restart reset the counters. Validate with explain and db.collection.aggregate([{$indexStats:{}}]) before trusting or removing it. [source]
You can access the Performance Advisor with the [MongoDB MCP Server](https://www.mongodb.com/docs/mcp-server.md#std-label-what-is-mcp-server). This tool enables you to interact with your Atlas clusters in natural language from AI clients like Claude, Cursor, and Github Copilot. [source]
Access Performance Advisor with the MCP server [source]
After you've configured the MongoDB MCP Server and connected it to your Atlas cluster, you can use natural language to retrieve Performance Advisor recommendations. The following table illustrates example prompts that you can use: [source]
This metric **does not** reflect the MongoDB Search index usage. [source]
Triage heuristics from the docs: planSummary COLLSCAN or keysExamined 0 on a filtered query suggests a missing index; docsExamined much larger than docsReturned suggests an unselective index; usedIndex=false and hasSort=true flag unindexed sorts; large response length suggests adding projections. usedIndex does not reflect MongoDB Search index usage. [source]
If the `queryPlanner.winningPlan.inputStage.stage` is either `CLUSTERED_IXSCAN` or `IXSCAN`, the index was used. For more information on the explain plan output, see [Explain Results.](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results) [source]
The `winningPlan.queryPlan.inputStage.stage` is `DISTINCT_SCAN`, which indicates that the index was used. For more information on the explain plan output, see [Explain Results.](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results) [source]
The following example creates an index filter on the `orders` collection such that for queries that consist only of an equality match on the `status` field without any projection and sort, the query optimizer evaluates only the two specified indexes and the collection scan for the winning plan: [source]
The Query Performance Summary reports execution time, number of returned documents, number of examined documents and number of examined index keys. The aggregation pipeline builder's Explain button opens the same modal, with one tree node per pipeline stage, and shows the same four summary metrics. [source]
- To learn about the performance impact of indexes, see [Operational Factors and Data Models.](https://www.mongodb.com/docs/manual/data-modeling/best-practices.md#std-label-data-model-indexes) [source]
- If the Performance Advisor has recommendations, click View Recommendations button in the Create Indexes section. [source]
The Performance Advisor ranks the indexes according to their Impact, which is based on the total wasted bytes read by the associated operations. To learn more about index ranking, see [Review Index Ranking.](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#std-label-pa-index-ranking) [source]
The Performance Advisor ranks the indexes according to their Impact, which is based on the total wasted bytes read by the associated operations. To learn more about how the Performance Advisor ranks indexes, see [Review Index Ranking.](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#std-label-pa-index-ranking) [source]
The Performance Advisor includes a user feedback button for Index Suggestions on dedicated clusters. [source]
- You can only create one index at a time through the Performance Advisor. If you want to create more simultaneously, you can do so using the [Atlas UI](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md#std-label-atlas-ui-indexes), a [driver](https://www.mongodb.com/docs/atlas/driver-connection.md#std-label-connect-via-driver), or the [shell](https://www.mongodb.com/docs/atlas/mongo-shell-connection.md#std-label-connect-mongo-shell) [source]
- Atlas always creates indexes for entire clusters. If you create an index while viewing the Performance Advisor for a single shard in a sharded cluster, Atlas creates that index for the entire sharded cluster. [source]
The Performance Advisor opens the Create Index dialog box and prepopulates the Fields based on the index you selected. [source]
Returns the indexes that the Performance Advisor suggests. [source]
- [atlas api performanceAdvisor listClusterSuggestedIndexes](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listclustersuggestedindexes.md#std-label-atlas-api-performanceAdvisor-listClusterSuggestedIndexes) - Returns the indexes that the Performance Advisor suggests. [source]
- [atlas api performanceAdvisor listDropIndexSuggestions](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listdropindexsuggestions.md#std-label-atlas-api-performanceAdvisor-listDropIndexSuggestions) - Returns the indexes that the Performance Advisor suggests to drop. [source]
- [atlas api performanceAdvisor listSuggestedIndexes](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listsuggestedindexes.md#std-label-atlas-api-performanceAdvisor-listSuggestedIndexes) - Returns the indexes that the Performance Advisor suggests. [source]
The Performance Advisor orders the [indexes](https://www.mongodb.com/docs/manual/indexes/) that it suggests by their respective Impact. Impact indicates the estimated performance improvement that the suggested index would bring. [source]
The Performance Advisor doesn't suggest indexes that have more than 16 fields. [source]
Also, the Performance Advisor suggests the index only if the difference between scanned documents and returned documents is greater than 500 for impacted queries. [source]
The Performance Advisor de-duplicates overlapping indexes before it makes suggestions. For example, consider if the Performance Advisor calculates the following potential suggested indexes: [source]
The Performance Advisor lists recommended indexes in order of performance impact, from greatest to least. [source]
Click the Create Index button to create the index with any desired additional options. To learn more about creating an index in the Performance Advisor, see [Create Suggested Indexes.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-pa-create-suggested-indexes) [source]
Index Build Impact on Database Performance [source]
You can remove a specific index from a collection. You may need to drop an index if you see a negative performance impact, want to replace it with a new index, or no longer need the index. [source]
If you drop an index that's actively used in production, you may experience performance degradation. Before you drop an index, consider [hiding the index](https://www.mongodb.com/docs/manual/core/index-hidden.md#std-label-index-type-hidden) to evaluate the potential impact of the drop. [source]
The [`serverStatus`](https://www.mongodb.com/docs/manual/reference/command/serverstatus.md#mongodb-dbcommand-dbcmd.serverStatus) command, or [`db.serverStatus()`](https://www.mongodb.com/docs/manual/reference/method/db.serverstatus.md#mongodb-method-db.serverStatus) from the shell, returns a general overview of the status of the database, detailing disk usage, memory use, connection, journaling, and index access. The command returns quickly and does not impact MongoDB performance. [source]
- To learn how sort order (ascending or descending) impacts performance of compound indexes, see [Use Indexes to Sort Query Results.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#std-label-sorting-with-indexes) [source]
Total number of index keys inserted by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Total number of index keys deleted by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
The Performance Advisor orders the indexes that it suggests by their respective Impact. Impact indicates the estimated performance improvement that the suggested index would bring. [source]
How Performance Advisor Suggests and Ranks Indexes [source]
Click the Create Index button to create the index with any desired additional options. To learn more about creating an index in the Performance Advisor, see Create Suggested Indexes. [source]
Indexes have some negative performance impact on write operations. For collections with high write-to-read ratio, indexes are expensive since each insert must also update any indexes. For a detailed list of considerations for indexes, see [Operational Considerations for Indexes.](https://www.mongodb.com/docs/manual/data-modeling/best-practices.md#std-label-data-model-indexes) [source]
The Performance Advisor suggests dropping unused, redundant, and hidden indexes to improve write performance and increase storage space. [source]
The Performance Advisor flags an index as unused if it hasn't supported a query in 7 or more days after it was created or the server was restarted. [source]
The Performance Advisor inspects only the 20 most active collections for unused indexes. [source]
The Performance Advisor flags an index as redundant if it matches the prefix of another index in the same collection. [source]
In the Performance Advisor, redundant indexes are marked with a red `Redundant` badge. Below each redundant index, the Performance Advisor displays the related indexes that cover it. [source]
Atlas doesn't use hidden indexes to support queries. They still impact write performance and consume storage space. To learn more, see [Hidden Indexes.](https://www.mongodb.com/docs/manual/core/index-hidden/) [source]
The Performance Advisor always recommends dropping hidden indexes. If you determine that a hidden index is unnecessary, drop it. [source]
The Atlas Performance Advisor page displays hidden indexes from your top 20 most active namespaces at the time you load the page. If you have more than 20 namespaces, the Performance Advisor page might not display all of your namespaces with hidden indexes. [source]
In the Performance Advisor tab, click Explore Recommendations on the Drop Indexes card. [source]
The Performance Advisor displays a dialog box with a link to the [Atlas UI](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md#std-label-atlas-ui-indexes) and a copyable [MongoDB Shell](https://www.mongodb.com/docs/mongodb-shell/) command to drop that index. [source]
If you change the weights in your index after it is created, MongoDB needs to reindex the collection. Reindexing can negatively impact performance, especially on large collections. For more information, see [Index Builds on Populated Collections.](https://www.mongodb.com/docs/manual/core/index-creation.md#std-label-index-creation-background) [source]
Hashed indexes truncate floating-point numbers to 64-bit integers before hashing. For example, a hashed index uses the same hash to store the values `2.3`, `2.2`, and `2.9`. This is a **collision**, where multiple values are assigned to a single hash key. Collisions may negatively impact query performance. [source]
The Performance Advisor ranks the indexes according to their Impact, which is based on the total wasted bytes read by the associated operations. To learn more about how the Performance Advisor ranks indexes, see Review Index Ranking. [source]
You can only create one index at a time through the Performance Advisor. If you want to create more simultaneously, you can do so using the Atlas UI, a driver, or the shell [source]
Returns the indexes that the Performance Advisor suggests to drop. [source]
The Compass intelligent assistant interprets explain plan output for a Compass query: in MongoDB's example it flags partial index use and high documents examined, recommends a compound index and proposes a revised pipeline. [source]
| Metric | Description | | --- | --- | | Execution Count | Number of queries executed per hour which would be improved. | | Average Execution Time | Current average execution time in milliseconds for affected queries. | | Average Query Targeting | Average number of documents read per document returned by affected queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see [Query Targeting.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-query-targeting) | | In Memory Sort | Current number of affected quer [source]
1. For the index you want to create, click Create Index. [source]
2. *(Optional)* Specify the [index options](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#std-label-createIndex-options). [source]
The following options document specifies the `unique` option and the `name` for the index: [source]
3. *(Optional)* Set the [Collation](https://www.mongodb.com/docs/manual/indexes/#indexes-and-collation) options. [source]
4. *(Optional)* Enable building indexes in a rolling fashion. [source]
The Atlas UI doesn't support building indexes with a rolling build for Free clusters and Flex clusters. [source]
In the event of a rolling index build cancellation, Atlas generates an [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed.md#std-label-view-activity-feed) and sends a notification email to the project owner with the following information: [source]
- Name of the cluster on which the rolling index build failed [source]
- Namespace on which the rolling index build failed [source]
6. In the Confirm Operation dialog box, confirm your index. [source]
- Name of the cluster on which the index build completed [source]
- Namespace on which the index build completed [source]
An index is redundant if another index supports any query that it could. [source]
Related indexes are displayed for you to verify that the redundant index can be dropped safely. Related indexes are not recommended for removal. [source]
9.0 restructures $queryStats output, grouping metrics under new fields such as metrics.queryExec and metrics.queryPlanner. Tooling that parses $queryStats output must be re-checked before upgrading. [source]
9.0 lets a querySettings document be passed directly on a command (applies to that execution only, stored cluster settings win on conflict), adds a queryKnobs setting (requires FCV 9.0) to override internal parameters for one query shape, and a per-shape maxTimeMS via setQuerySettings. 8.1 (and 8.0.4) added comments on setQuerySettings. [source]
The [query optimizer](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-optimizer) uses query settings as an additional input during query planning. Index hints in query settings restrict the set of indexes available to the planner, but don't guarantee that the planner will use the index. The planner can still select a collection scan as the winning plan for a given query shape hash. [source]
For the plan cache query shape, the query optimizer will only consider indexed plans which use the index `{ item: 1, order_date: 1, quantity: 1 }`. [source]
For the plan cache query shape, the query optimizer only uses indexed plans that use the index `{ item: 1, order_date: 1, quantity: 1 }`. [source]
MongoDB Compass provides an Explain Plan modal, which displays statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index. [source]
At the bottom of the Query Performance Summary, MongoDB Compass shows that the query used the year index. [source]
- Building a text index is similar to building a large [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multi-key) but takes longer than building an ordered (scalar) index on the same data. [source]
- The query is unsupported by your current indexes. [source]
- Some documents in your collection have large array fields that are costly to search and index. [source]
The following methods provide overviews of your deployment's performance. Use these methods to determine if there are performance issues that need to be addressed: [source]
The following example creates an index filter for the `orders` collection. The filter applies to queries whose predicate is an equality match on the `item` field, where only the `quantity` field is projected and an ascending sort by `order_date` is specified. [source]
The following example creates an index filter for the `orders` collection. The filter applies to queries whose predicate is an equality match on the `item` field and the collation `en_US` (English United States). [source]
Starting in MongoDB 6.0, an index filter uses the [collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) previously set using the [`planCacheSetFilter`](https://www.mongodb.com/docs/manual/reference/command/plancachesetfilter.md#mongodb-dbcommand-dbcmd.planCacheSetFilter) command. [source]
- [Monitor real-time performance](https://www.mongodb.com/docs/atlas/real-time-performance-panel.md#std-label-real-time-metrics-status-tab) with the Real-Time Performance Panel. [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.pipeline` | Array | Normalized | | `key.queryShape.explain` | Boolean | Literal | | `key.queryShape.allowDiskUse` | Boolean | Literal | | `key.queryShape.allowPartialResults` | Boolean | Literal | | `key.queryShape.collation` | Document | Literal | | `key.queryShape.hint` | String or Document | Normalized | | `key.queryShape.let` | Document | Normalized | [source]
Starting in MongoDB 8.0, `explain` outputs the following field: [source]
- Keys Examined: The total number of index entries scanned by the database during query execution. [source]
5. Interpret performance metrics. [source]
- If you see `"planSummary": "COLLSCAN":` in the query log, the query performed a collection scan and did not use an index. This is a strong signal to add an [index](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) or rewrite the query. [source]
- Existing indexes are inefficient or not optimal for this specific query. To learn more, see [Indexes.](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) [source]
- Uses Index (`usedIndex`): This boolean confirms whether a MongoDB index was used. If set to `false` for a query that should be indexed, add an [index.](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) [source]
Clicking Explain in the Compass query bar opens the Explain Plan modal. It shows a Visual Tree by default, with one node per query operation. Clicking a node shows detailed execution information. A Raw Output view shows the full explain plan as JSON. [source]
The Compass docs page does not state which explain verbosity Compass uses. The summary's runtime figures imply executionStats-level data, but this is inference. The equivalent shell call is db.collection.find(...).explain('executionStats'). Compass presents the same data as the shell in a GUI. [source]
> Derived 2026-10-19; topic-filtered subset of mongodb.com-docs-query.md: Server manual pages about queries, indexes, aggregation, time series, profiling, explain, parameters and 7.x–9.0 release notes, plus Compass query/explain/index pages and Atlas performance/query pages. Third-party text; private reading aid; never publish. [source]
- The recommended approach is to manually delete documents in small batches before updating the TTL index. This helps control the impact on your cluster. [source]
Returns the schema suggestions that the Performance Advisor detects. [source]
This section summarizes the storage and write impact of encrypting collections, and explains how to compact encrypted collection indexes to minimize these costs. If you want to encrypt fields and configure them for querying, see [Encrypted Fields and Enabled Queries.](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#std-label-qe-fundamentals-encrypt-query) [source]
Equality queries carry fixed additional costs for storage and write operations. Range, prefix, suffix, and substring query costs depend on the queryable field's [parameters](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#std-label-qe-field-configuration). Tightly bounding these queries greatly decreases their performance impact. [source]
- For encrypted fields with range queries enabled, review the field configuration, especially the [min, max,](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#std-label-qe-field-min-max) and [precision](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#std-label-qe-field-precision) parameters. Setting tight bounds for range queries greatly decreases the performance impact of those fields. [source]
Do not run production [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instances with `ttlMonitorEnabled` disabled, except under guidance from MongoDB support. Preventing TTL document removal can negatively impact MongoDB internal system operations that depend on TTL Indexes. [source]
Using a large value, or disabling truncation with a value of `0`, may adversely affect system performance and negatively impact database operations. [source]
- [atlas api performanceAdvisor listSchemaAdvice](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-performanceadvisor-listschemaadvice.md#std-label-atlas-api-performanceAdvisor-listSchemaAdvice) - Returns the schema suggestions that the Performance Advisor detects. [source]
MongoDB offers the ability to hide or unhide indexes from the query planner. By hiding an index from the planner, you can evaluate the potential impact of dropping an index without actually dropping the index. [source]
- If using `tuned` on RHEL / CentOS, you must customize your `tuned` profile. Many of the `tuned` profiles that ship with RHEL / CentOS can negatively impact performance with their default settings. Customize your chosen `tuned` profile to: [source]
If the batch size is too large, the cursor allocates more resources than it requires, which can negatively impact query performance. If the batch size is too small, the cursor requires more network round trips to retrieve the query results, which can negatively impact query performance. [source]
Total number of documents matched by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Total number of documents modified by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Total number of documents upserted by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Total number of documents inserted by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Total number of documents deleted by queries with the given query shape. Available only for write commands on clusters running MongoDB 9.0 or later. [source]
Ideally, the ratio of scanned documents to returned documents should be close to 1. A high ratio negatively impacts query performance. [source]
**Tip: Remove Redundant Indexes** [source]
You can [hide an index](https://www.mongodb.com/docs/manual/core/index-hidden/) from the query planner to evaluate the potential impact of dropping an index without actually dropping the index. [source]
You can hide an index to evaluate the impact of dropping an index before you drop it. Unhiding an index also takes less time than rebuilding a dropped index. To hide and unhide an index by using the Atlas UI, see [Manage Atlas Indexes.](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md#std-label-atlas-ui-indexes) [source]
Consider [hiding indexes](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#std-label-hidden-indexes) before you drop them. [source]
[1] For single-field indexes, the order of the index doesn't matter. For compound indexes, the field order impacts what queries the index supports. For details, see Compound Index Sort Order. [source]
For single-field indexes, the order of the index doesn't matter. For compound indexes, the field order impacts what queries the index supports. For details, see [Compound Index Sort Order.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#std-label-index-ascending-and-descending) [source]
The [WiredTiger](https://www.mongodb.com/docs/manual/core/wiredtiger.md#std-label-storage-wiredtiger) storage engine is multithreaded and can take advantage of additional CPU cores. Specifically, the total number of active threads (concurrent operations) relative to the number of available CPUs can impact performance: [source]
The Pause button above the graph display pauses the update of the displays only. Pausing the performance tab does not impact the collection of the underlying data. To restart the display updates, click Play. [source]
You may experience performance limitations from inadequate or inappropriate indexing strategies or poor schema design patterns. [Locking Performance](https://www.mongodb.com/docs/manual/administration/analyzing-mongodb-performance.md#std-label-analyzing-performance-locks) discusses how these can impact MongoDB's internal locking. [source]
Query-only automatic index suggestion for MongoDB, derived from aggregation pipelines without data or usage statistics, was shown feasible in ADBIS 2023 (Espona et al., FHNW): on TPC-H all suggested indexes were used. This is non-LLM prior art. [source]
The examples in the following sections use queries against the `sample_mflix.movies` collection in the [sample data](https://www.mongodb.com/docs/atlas/import/load-sample-data.md#std-label-sample-data) to illustrate the improvements to flexibility and performance that MongoDB Search offers over [`$text`](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#mongodb-query-op.-text). You can run the queries from both examples using the following indexes: [source]
You can create a text index to improve the performance of text queries run on a collection containing documents or embedded documents with text in multiple languages. [source]
By default, the `default_language` for text indexes is `english`. To improve the performance of non-English `$text` queries, you can specify a different default language associated with your text index. [source]
Index use and performance for `$regex` queries depends on whether the query is case-sensitive or case-insensitive. [source]
MongoDB 8.2 removes `profileOperationResourceConsumptionMetrics`. Therefore, code depending on [operationMetrics](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-executionStats) does not work. [source]
Known issue at FCV 9.0: listIndexes reports collation { locale: 'simple' } for simple-collation indexes ([redacted]), so Mongoose syncIndexes() (also Doctrine ODM, Beanie) drops and rebuilds unchanged indexes. Workaround: do not call syncIndexes() at startup, or use Mongoose 9.10.4 / 8.24.5. Fix targeted for a future 9.0 patch. [source]
Index filters only affect which indexes the planner evaluates; the planner may still select the collection scan as the winning plan for a given plan cache query shape. [source]
You can use query settings to add index hints, define [operation rejection filters](https://www.mongodb.com/docs/manual/tutorial/operation-rejection-filters.md#std-label-operation-rejection-filters), and set other fields for all executions of a given [query shape](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) on a cluster. A cluster's query settings persist across restarts. [source]
The following examples create a collection and add query settings for different commands. For all executions of a query shape on the cluster, the examples restrict the query planner to either using the hinted index, or a collection scan. [source]
You can also monitor collection-level [query latency](https://www.mongodb.com/docs/manual/reference/operator/aggregation/collStats/#latencystats-document) with [Namespace Insights](https://www.mongodb.com/docs/atlas/namespace-insights.md#std-label-namespace-insights) and query performance with the [Query Profiler.](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler) [source]
Profiling can degrade performance and expose unencrypted query data in the system log. Carefully consider any performance and security implications before configuring and enabling the profiler on a production deployment. [source]
See [Profiler Overhead](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiling-overhead) for more information on potential performance degradation. [source]
Increasing the plan cache size adds more cached [plan cache query shapes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) for the [query planner](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-read-operations-query-optimization). This can improve query performance, but increases memory usage. [source]
The [query optimizer](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-optimizer) uses the query settings as an additional input during query planning, which affects the plan selected to run the query. You can also use query settings to block a query shape. [source]
query settings, which you can add with setQuerySettings. (As you'll see later on this page, query settings specify indexes and execution settings for a query shape.) [source]
Query settings allow you to use an index for all executions of a query shape in a cluster. Also, to prevent an operation from causing excessive cluster workload, you can reject all operations associated with a query shape using an operation rejection filter. [source]
The query optimizer uses the query settings as an additional input during query planning. The query settings affect the plan selected to run a query that has a matching query shape. [source]
On the Explain modal, you can view the explain stages as a Visual Tree, where each stage of the pipeline appears as a node on the tree. Alternatively, you can view the explain details in raw JSON format by selecting the Raw Output view. [source]
In the top right of the aggregation pipeline builder, click the Explain button to open the Explain Plan modal. [source]
By default, the explain stages are are shown as a Visual Tree. Each stage of the pipeline appears as a node on the tree. [source]
You can use query settings to set index hints, set operation rejection filters, and other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown. [source]
The Namespace Insights page in the Atlas UI allows you to monitor collection-level performance and usage metrics. It displays metrics (such as the number of CRUD operations on the collection) and statistics (like average query execution time) for certain hosts and operation types for the collections that you [pin for monitoring](https://www.mongodb.com/docs/atlas/namespace-insights/#manage-pinned-namespaces-from-the-dialog). This gives you more granular visibility into collection-level performance, which you can use to optimize database performance, resolve issues, and make decisions about sca [source]
By default, the explain stages are are shown as a Visual Tree. Each query operation appears as a node on the tree. [source]
For example, the following explain plan provides detailed information on a query that filters for `{ title : "Jurassic Park" }`: [source]
Query shape statistics provide performance insights about MongoDB queries, helping users identify problematic query patterns and potential optimizations. [source]
Explain plan results for queries are subject to change between MongoDB versions. [source]
You can see the optimized pipeline in the [explain plan.](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#std-label-example-aggregate-method-explain-option) [source]
You can see this optimized pipeline in the [explain plan.](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#std-label-example-aggregate-method-explain-option) [source]
Total number of in-bounds and out-of-bounds index keys examined by queries with the given query shape. [source]
Ratio of in-bounds and out-of-bounds index keys examined to indexes containing documents returned by queries with the given query shape. [source]
Atlas might not [log](https://www.mongodb.com/docs/atlas/mongodb-logs.md#std-label-mongodb-logs) the individual operations that contribute to the Query Targeting ratios due to automatically set thresholds. However, you can still use the Query Profiler and Query Targeting metrics to analyze and optimize query performance. [source]
Specify the [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) to remove a specific index filter. Omit the plan cache query shape to clear all index filters on a collection. [source]
Lists the [index filters](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) associated with [plan cache query shapes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) for a collection. [source]
The array of indexes for the plan cache query shape. [source]
Both the Atlas Query Profiler and the database profiler can affect server performance, use up disk space, and expose query metadata around encrypted fields. Consider the performance and security implications before enabling them. [source]
A boolean that specifies whether MongoDB applied an [index filter](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) for the [plan cache query shape.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) [source]
For more information on execution statistics of collection scans, see [Interpret Explain Plan Results.](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md) [source]
You can also monitor collection-level query latency with Namespace Insights and query performance with the Query Profiler. [source]
- query settings, which you can add with [`setQuerySettings`](https://www.mongodb.com/docs/manual/reference/command/setquerysettings.md#mongodb-dbcommand-dbcmd.setQuerySettings). (As you'll see later on this page, query settings specify indexes and execution settings for a query shape.) [source]
For more information on execution statistics of collection scans, see Interpret Explain Plan Results. [source]
Atlas might not log the individual operations that contribute to the Query Targeting ratios due to automatically set thresholds. However, you can still use the Query Profiler and Query Targeting metrics to analyze and optimize query performance. [source]
Monitor Query Performance with the Query Profiler [source]
The following example returns the index named `synonym-mappings` on the `movies` collection: [source]
3. Run the [atlas clusters search nodes create](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-clusters-search-nodes-create.md#std-label-atlas-clusters-search-nodes-create) command with the `--file` option. [source]
3. Run the [atlas clusters search nodes update](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-clusters-search-nodes-update.md#std-label-atlas-clusters-search-nodes-update) command with the `--file` option. [source]
The `{ "content": "text" }` index only includes the `content` field, and does not return matches on non-indexed fields. For example, the following query searches the `blog` collection for the string `food`: [source]
MongoDB documentation recommends rewriting $text-heavy queries as $search to gain flexibility and performance (language awareness, fuzzy matching, highlighting, autocomplete, synonyms, relevance scoring), with the gain growing as data size and query breadth grow. The same page calls $text adequate for simple, narrow searches, so migration is not advised for every query. [source]
Before you modify your schema based on performance insights, ensure that the suggestion makes sense for your application. For example, if Atlas suggests creating an index, make sure that index supports queries that are run frequently. [source]
The Real-Time Performance Panel monitors and displays current network traffic and database operations on machines hosting MongoDB in your Atlas clusters. [source]
Atlas provides several tools that allow you to proactively monitor and improve the performance of your database. [source]
MongoDB provides several ways to examine the performance of your workload, allowing you to understand query performance and identify long-running queries. Understanding query performance helps you build effective indexes and ensure your application runs critical queries efficiently. [source]
Atlas provides several metrics for monitoring and alerting. You can track the health, availability, consumption, and performance of your deployments in visual dashboards and by API (Application Programming Interface). You can also view various cluster metrics, monitor your database performance, configure alerts and alert notification, and download activity logs. [source]
- Deleting many documents can fragment storage files, additionally impacting performance. You may need to run the [compact](https://www.mongodb.com/docs/manual/reference/privilege-actions.md#mongodb-authaction-compact) command on your collection or perform a [Initial Sync](https://www.mongodb.com/docs/manual/core/replica-set-sync.md#std-label-replica-set-initial-sync) to reclaim space and optimize storage. [source]
- Use a [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) stage in an [aggregation pipeline](https://www.mongodb.com/docs/manual/core/aggregation-pipeline.md#std-label-aggregation-pipeline) and examine the `queryShapeHash` field. [source]
- A link to the report subscription settings in Project Settings. To learn more, see [Manage Project Settings.](https://www.mongodb.com/docs/atlas/tutorial/manage-project-settings.md#std-label-project-settings) [source]
The following example sets for a [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) or [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) instance: [source]
For example, this [`$in`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/in.md#mongodb-expression-exp.-in) query benefits from a secondary index on `metadata.type`: [source]
Sort operations on time series collections can use secondary indexes on the `timeField` field. Under certain conditions, sort operations can also use compound secondary indexes on the `metaField` and `timeField` fields. [source]
The aggregation pipeline uses the [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) and [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) stages to determine which indexes a time series collection can use. An index can be used in the following scenarios: [source]
- A sort on `{ <timeField>: ±1 }` uses a secondary index on `<timeField>`. [source]
- A sort on `{ <metaField>: ±1, timeField: ±1 }` uses the default compound index on `{ <metaField>: ±1, timeField: ±1 }`. [source]
The following command creates a compound secondary index on `metaField` (ascending) and `timeField` (descending): [source]
The following last point query example uses the descending `timeField` compound secondary index created above: [source]
On a time series collection, you can specify hints using either the index name or the index key pattern. To get the names of the indexes on a collection, use the [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getindexes.md#mongodb-method-db.collection.getIndexes) method. [source]
Starting in version 6.0 you can create 2dsphere indexes on the `timeField`, `metaField`, or measurement fields. For example, the following operation creates a 2dsphere index on the `location` field: [source]
If there are [secondary indexes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-secondary-index) on [time series collections](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-collection) and you need to downgrade the feature compatibility version (FCV), you must first drop any secondary indexes that are incompatible with the downgraded FCV. For more information, see [`setFeatureCompatibilityVersion`.](https://www.mongodb.com/docs/manual/reference/command/setfeaturecompatibilityversion.md#mongodb-dbcommand-dbcmd.setFeatureCompati [source]
The aggregation pipeline stages [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) and [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) determine which indexes a time series collection can use. An index can be used in the following scenarios: [source]
- Sort on `{ <timeField>: ±1 }` uses a secondary index on [source]
compound index on `{ <metaField>: ±1, timeField: ±1 }` [source]
On a time series collection, you can specify hints using either the index name or the index key pattern. To get the names of the indexes on a collection, use `GetIndexes()`. [source]
Starting in version 6.0 you can create `2dsphere` indexes. For example, the following operation creates a 2dsphere index on the `location` field: [source]
Starting in version 6.0, you can create 2dsphere indexes on the `timeField`, `metaField`, or measurement fields. For example, the following operation creates a 2dsphere index on the `location` field: [source]
The maximum number of milliseconds to wait for a lock acquisition before performing writes to [`profile`](https://www.mongodb.com/docs/manual/reference/system-collections.md#mongodb-data--database-.system.profile) collections. [source]
The following example shows how MongoDB uses bounds intersection to define a smaller range of values to query, resulting in improved query performance. [source]
The following example shows how MongoDB uses compound bounds to define a more efficient query constraint, resulting in improved query performance. [source]
- Use sequentially increasing key values to improve insert performance. [source]
Randomly generated key values may decrease a clustered collection's performance. [source]
If you use the `_id` field in a range query, performance is improved. For example, the following query uses `_id` and [`$gt`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/gt.md#mongodb-expression-exp.-gt) to return the orders where the order date is greater than the supplied date: [source]
2. Create a [sparse index](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) on the `metacritic` field: [source]
3. The following example counts the documents where the `metacritic` field has a value (including null) and uses the [sparse index:](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) [source]
- Create queries that your current indexes support to reduce the time needed to search for your results. [source]
- Avoid creating documents with large array fields that require a lot of processing to search and index. [source]
- [`queryPlanner.winningPlan.stage`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.winningPlan.stage) displays `COLLSCAN` to indicate a collection scan. [source]
- [`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.nReturned) displays `3809` to indicate that the winning query plan returns 3,809 documents. [source]
- [`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined) displays `0` to indicate that this query is not using an index. [source]
- [`executionStats.totalDocsExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalDocsExamined) displays `21349` to indicate that MongoDB had to scan all 21,349 documents in the collection to find the 3,809 matching documents. [source]
The difference between the number of matching documents and the number of examined documents may suggest that the query might benefit from the use of an index to improve efficiency. [source]
- [`queryPlanner.winningPlan.inputStage.stage`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.winningPlan.inputStage) displays `IXSCAN` to indicate index use. [source]
- [`executionStats.totalDocsExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalDocsExamined) display `3809` to indicate that MongoDB scanned 3,809 documents. [source]
For example, add the following two compound indexes. The first index orders by `year` field first, and then the `rated` field. The second index orders by `rated` first, and then the `year` field. [source]
MongoDB scanned `246` index keys ([`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined)) to return `235` matching documents ([`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.nReturned)). [source]
MongoDB scanned `235` index keys ([`executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined)) to return `235` matching documents ([`executionStats.nReturned`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.nReturned)). [source]
- An index on the `$match` query field efficiently identifies the relevant data [source]
- An index on the sorting field returns data in sorted order for the `$sort` stage [source]
- An index on the grouping field that matches the `$sort` order returns all of the field values needed for the `$group` stage, making it a covered query. [source]
In some cases, the query planner uses a `DISTINCT_SCAN` index plan that returns one document per index key value. `DISTINCT_SCAN` executes faster than `IXSCAN` if there are multiple documents per key value. However, index scan parameters might affect the time comparison of `DISTINCT_SCAN` and `IXSCAN`. [source]
During the `$match` stage, the server can use an index if `$match` is the first stage in the pipeline, after any optimizations from the [query planner.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plans-query-optimization) [source]
During the `$sort` stage, the server can use an index if the stage is not preceded by a [`$project`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project), [`$unwind`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unwind.md#mongodb-pipeline-pipe.-unwind), or [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage. [source]
During the `$group` stage, the server can use an index to quickly find the [`$first`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/first.md#mongodb-group-grp.-first) or [`$last`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/last.md#mongodb-group-grp.-last) document in each group if the stage meets both of these conditions: [source]
- The pipeline [`sorts`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) and [`groups`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) by the same field. [source]
- The `$group` stage only uses the [`$first`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/first.md#mongodb-group-grp.-first) or [`$last`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/last.md#mongodb-group-grp.-last) accumulator operator. [source]
The server always uses an index for the `$geoNear` stage, since it requires a [geospatial index.](https://www.mongodb.com/docs/manual/geospatial-queries.md#std-label-index-feature-geospatial) [source]
Additionally, stages later in the pipeline that retrieve data from other, unmodified collections can use indexes on those collections for optimization. These stages include: [source]
- [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) followed by [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) at the start of the pipeline is equivalent to a single query with a sort, and can use an index. [source]
You can specify the amount of time to wait before abandoning writes to [`profile`](https://www.mongodb.com/docs/manual/reference/system-collections.md#mongodb-data--database-.system.profile) collections by setting the [`internalQueryGlobalProfilingLockDeadlineMs`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.internalQueryGlobalProfilingLockDeadlineMs) parameter. [source]
MongoDB runs the [query optimizer](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-read-operations-query-optimization) to choose the winning plan, executes the winning plan to completion, and returns statistics describing the execution of the winning plan. [source]
- Parameter names such as the `partitionBy` parameter in [`$setWindowFields`.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/setwindowfields.md#mongodb-pipeline-pipe.-setWindowFields) [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.collation` | Document | Normalized | | `key.queryShape.key` | String | Literal | | `key.queryShape.query` | Document | Normalized | [source]
| Field | Type | Literal or Normalized | | --- | --- | --- | | `key.queryShape.q` | Document | Normalized | | `key.queryShape.u` | Document or Array | Normalized | | `key.queryShape.c` | Document | Normalized | | `key.queryShape.arrayFilters` | Array of documents | Normalized | | `key.queryShape.multi` | Boolean | Literal | | `key.queryShape.upsert` | Boolean | Literal | | `key.queryShape.let` | Document | Normalized | | `key.queryShape.collation` | Document | Literal | [source]
Querying *only* the index can be much faster than querying documents outside of the index. Index keys are typically smaller than the documents they catalog, and indexes are typically available in RAM or located sequentially on disk. [source]
Time in milliseconds the query planner spent on query optimization. This result doesn't include time spent optimizing inner [`$lookup`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup) queries. [source]
A document that details the plan selected by the [query optimizer.](https://www.mongodb.com/docs/manual/core/query-plans.md) [source]
A document that details the plan selected by the [query optimizer](https://www.mongodb.com/docs/manual/core/query-plans.md). MongoDB presents the plan as a tree of stages. [source]
Array of candidate plans considered and rejected by the query optimizer. The array can be empty if there were no other candidate plans. [source]
In such cases, during the initial read of the documents, MongoDB pages the documents into memory such that subsequent calls of the same count operation will have better performance. [source]
- The same fields as a [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find), [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct), or [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) command. See the syntax sections on the pages for those commands for the fields you can include in `removeQuerySettings`. [source]
The Query Profiler data is sampled and lossy: about 100,000 sampled logs and at most 100,000 chart points at once; a bucketing algorithm means a wider time range can show fewer sampled operations than a narrower one; data can lag real time by up to five minutes; during an activity spike Atlas may stop collecting new logs for a period. For exact counts, download the mongod logs, which are always complete. [source]
Bulk operations (bulkWrite(), updateMany with multiple stmtIds, similar multi-document operations) are excluded from Query Profiler sampling and analysis, so a bulk-write-heavy workload is under-represented. [source]
Profiler views: scatterplot plus table over the last 24 hours by default, filterable by host or primary/secondary group and operation type. Selectable metrics: execution time, response length, docs/keys examined, docs returned, numYields, tokens used ($rerank), and docs-examined:returned and keys-examined:returned ratios. Operations are binned by type, namespace, hasSort, usedIndex and timestamp. Clicking a point shows query shape, execution stats, log and improvement suggestions. [source]
- On clusters running MongoDB 8.x, Atlas records up to 100 read queries per second on each host. Atlas does not collect write operations. [source]
View real-time performance metrics for a specific Atlas cluster in a project. [source]
You can also view Query Targeting ratios of operations in real-time using the Real-Time Performance Panel. [source]
Setting this parameter makes brute-force login attacks on a database more time-consuming. However, clients waiting for a response from the MongoDB server still consume server resources, and this may adversely impact benign login attempts if the server is denying access to many other clients simultaneously. [source]
This parameter only affects X.509 authentication. It has no impact on other authentication mechanisms. [source]
For improved memory usage, we recommend using the default value of `true`. To learn more about improvements to performance and memory management, see [Upgraded TCMalloc.](https://www.mongodb.com/docs/manual/release-notes/8.0.md#std-label-8.0-tcmalloc-upgrade) [source]
Starting in MongoDB 8.0, the default value of `tcmallocReleaseRate` is reduced to `0` due to a [tcmalloc upgrade](https://www.mongodb.com/docs/manual/release-notes/8.0.md#std-label-8.0-tcmalloc-upgrade) that prioritizes CPU performance over memory release. [source]
If the [`initialSyncMethod`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.initialSyncMethod) parameter for the cluster is `fileCopyBased`, then there is no impact on change stream listeners. [source]
Your choice of `metaField` has the biggest impact on optimizing queries in your application. [source]
Enabling Queryable Encryption on your collection will likely have a smaller disk storage and memory impact than calculated. These formulas guard against the worst case. [source]
Performance insights direct you to the [Intelligent Assistant in Data Explorer](https://www.mongodb.com/docs/atlas/atlas-ui/query-with-natural-language/data-explorer-ai-assistant.md#std-label-de-ai-assistant), which can provide additional help with your schema or queries. [source]
A term match in the `content` field has `10` times the impact (`10:1` weight) as a term match in the `keywords` field. [source]
A term match in the `keywords` field has `5` times the impact (`5:1` weight) as a term match in the `about` field. [source]
MongoDB groups documents with matching [metaFields](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-timeseries-collections-metafield) to optimize the storage and query latency of time series data. Your choice of `metaField` has the biggest impact on optimizing queries in your application. [source]
Starting in MongoDB 9.0, `explain` outputs the following field: [source]
- [The MySQL "swap insanity" problem and the effects of NUMA](http://jcole.us/blog/archives/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/) post, which describes the effects of NUMA on databases. The post introduces NUMA and its goals, and illustrates how these goals are not compatible with production databases. Although the blog post addresses the impact of NUMA for MySQL, the issues for MongoDB are similar. [source]
For detailed examples with sample outputs, see [Performance Optimization.](https://www.mongodb.com/docs/mcp-server/local-mcp/examples.md#std-label-mcp-server-performance-examples) [source]
Performance insights are enabled automatically. [source]
The Real-Time Performance Panel (RTPP) in the Atlas UI provides insights into current network traffic, database operations, and hardware statistics about the hosts at a one second granularity in the Atlas UI. We recommend that you use RTPP (Real-Time Performance Panel) to: [source]
Database profiler output, in the command.comment field. [source]
Returns the details for a single query shape. [source]
Both ranking mechanisms store the chosen plan in the **query plan cache** and reuse it for subsequent queries with the same [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape). The rest of this page describes plan cache behavior and related concepts that apply to both mechanisms. [source]
You can provide an existing query shape hash string in `setQuerySettings` and an updated `settings` document with `indexHints` and other fields: [source]
In current supported versions, the same query shape is expected to produce the same query shape hash across nodes and deployment types. To learn how the hash behaves across nodes, deployment types, and clusters, see [Query Shape Hash Stability.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shape-hash-stability) [source]
Updates the rejection status of a query shape. [source]
Docs Home / Development / CRUD Operations / Read / Database Profiler [source]
The database profiler captures data information about read and write operations, cursor operations, and database commands. To configure the database profile and set the thresholds for capturing profile data, see the Database Profiler section. [source]
The database profiler writes data in the system.profile collection, which is a capped collection. To view the profiler's output, use normal MongoDB queries on the system.profile collection. [source]
For any single operation, the documents created by the database profiler includes a subset of the following fields. The precise selection of fields in these documents depends on the type of operation. [source]
Atlas supports Query Shape Insights on only dedicated clusters (M10 and above) running MongoDB version 8.0 and above. [source]
After you upgrade a cluster from MongoDB 8.x to 9.0, metric totals in Query Shape Insights may appear significantly different than before the upgrade. This difference reflects the change in sampling method, not a change in your cluster's workload. [source]
2. Go to Query Shape Insights. [source]
Click Query Insights in the Cluster sidebar. [source]
The Query Shapes tab displays by default. [source]
You can filter the Query Insights page by node, node state (primary or secondary), and shard. You can also filter by a custom date range within the last seven days, or select one of the following preset time ranges: [source]
[`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) users can also manually subscribe to or unsubscribe from Query Shape Reports regardless of whether the project has received a query targeting alert. To learn more, see [Manage Report Subscriptions.](https://www.mongodb.com/docs/atlas/query-shape-insights.md#std-label-query-shape-reports-manage) [source]
If no qualifying query shapes exist for a given reporting period, Atlas skips that report and resumes with the next scheduled report. [source]
- A link to the Query Shape Insights page for the cluster. [source]
- A table of up to five query shapes, ranked by total execution time. Each query shape must have at least 10 executions in the reporting period to appear. [source]
- A link to each query shape's details on the Query Shape Insights page. [source]
| Column | Description | | --- | --- | | Collection Name | Name of the collection that the query shape targets. | | Average Execution Time | Average time spent running queries with the given query shape. | | Execution Count | Total number of times that queries with the given query shape ran. | | Docs Examined:Returned Ratio | Ratio of documents examined to documents returned by queries with the given query shape. | [source]
You can subscribe to or unsubscribe from Query Shape Reports in your project's settings. This setting controls reports for all users in the project. Only users with the [`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) role can change the subscription, and only [`Project Owner`](https://www.mongodb.com/docs/atlas/reference/user-roles.md#mongodb-authrole-Project-Owner) users receive the reports. [source]
1. Toggle the Query Shape Reports setting. [source]
You can also unsubscribe by clicking the unsubscribe link in any Query Shape Report email. The link directs you to Project Settings, where Atlas unsubscribes the project. The unsubscribe link works without requiring you to log in. [source]
You can sort the Query Shape Insights table by one or more metric columns to help identify which query shapes to chart. [source]
You can select up to five query shapes at a time. [source]
The [profiler log messages](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) and [diagnostic log messages](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-messages-ref) includes a `usedDisk` indicator if any aggregation stage wrote data to temporary files due to [memory restrictions.](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-limits.md#std-label-agg-memory-restrictions) [source]
On [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod), if the [database profiler level](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) is `2`, full logging is enabled on the profiler and the [`diagnostic log`.](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--logpath) [source]
At [database profiler level](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) `1`, the following settings modify both the profiler and the [`diagnostic log`:](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--logpath) [source]
If the [database profiler level](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) is `0`, the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) is disabled. At level `0` the following settings only modify the diagnostic log: [source]
The profiler is off by default. [source]
Atlas Core clusters support all [database profiler levels.](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) [source]
The [`profile`](https://www.mongodb.com/docs/manual/reference/command/profile.md#mongodb-dbcommand-dbcmd.profile) command obtains a write lock on the affected database while enabling or disabling the profiler. This is typically a short operation. The lock blocks other operations until the [`profile`](https://www.mongodb.com/docs/manual/reference/command/profile.md#mongodb-dbcommand-dbcmd.profile) command has completed. [source]
You can set a `filter` without enabling the database profiler. The following example uses a filter with a [profiling level](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-level) of `0`. [source]
The fraction of read operations that MongoDB samples for [`$queryStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#mongodb-pipeline-pipe.-queryStats). The value must be between `0.0` and `1.0`. To sample all read operations, set the value to `1.0`. To stop sampling read operations, set the value to `0`. [source]
The fraction of write operations that MongoDB samples for [`$queryStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#mongodb-pipeline-pipe.-queryStats). The value must be between `0.0` and `1.0`. To sample all write operations, set the value to `1.0`. To stop sampling write operations, set the value to `0`. [source]
In MongoDB 8.0 and earlier versions, the existing plan cache query shape supports: [source]
Starting in MongoDB 8.0, the new query shape supports: [source]
$queryStats query shape statistics. [source]
the fields and operations also supported by the plan cache query shape. Examples: filter, sort, and projection. [source]
operation rejection filters to block find, aggregation, and distinct commands that have a specified query shape. [source]
Starting in MongoDB 8.0, you can use a $querySettings pipeline stage to return query settings specified for each query shape. [source]
In the example in the previous section, the example sorts by the totalNumber field. If you change the query sort from totalNumber to a different field, the query has a different query shape. [source]
MongoDB derives the queryShapeHash from the query shape itself, not from the deployment that runs the query. In current supported versions, the same query shape is expected to produce the same queryShapeHash: [source]
You can apply the same query settings to a query shape on every node in a deployment. Before you rely on query settings that you specify by query shape hash after a migration, such as from a replica set to a sharded cluster, or on a different cluster, verify that the hash still matches in the target deployment. [source]
4. (Optional) Select the Raw Output view [source]
Finds the distinct values for a specified field across a single collection. Returns a document containing an array of distinct values and an embedded document containing query statistics and the query plan. [source]
MongoDB doesn't store these settings on the cluster, and they don't apply to other queries with the same query shape. To set query settings that persist for a query shape, use setQuerySettings. [source]
MongoDB combines the settings that you pass in the command with any query settings stored on the cluster for the same query shape. If both settings specify the same field, the stored cluster setting takes precedence. [source]
Returns the metadata and statistics summary for a given query shape hash. [source]
The settings apply to the [query shape](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-queryShape) on the entire cluster. The cluster retains the settings after shutdown. [source]
Returns a list of query shape statistics summaries for a given cluster. [source]
Prior to version 7.2, information on the number of plan cache query shapes was stored in the `query.planCacheTotalQueryShapes` field. [source]
Total number of databases where the [Database Profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) is disabled after reaching the lock timeout threshold. [source]
You can specify the maximum number of lock timeouts per second that disable the profiler by setting the [`internalProfilingMaxAbandonedWritesPerSecondPerDb`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.internalProfilingMaxAbandonedWritesPerSecondPerDb) parameter. [source]
Number of queries that the `$queryStats` virtual collection has evicted due to space contraints. [source]
Current estimated size of objects in the `$queryStats` virtual collection. [source]
Configures the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md) level. The following profiler levels are available: [source]
The profiler is off and does not collect any data. This is the default profiler level. [source]
The profiler collects data for operations that exceed the `slowms` threshold or match a specified [filter.](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-options-filter) [source]
- The profiler only captures operations that match the [filter.](https://www.mongodb.com/docs/manual/reference/method/db.setprofilinglevel.md#std-label-set-profiling-level-options-filter) [source]
The profiler collects data for all operations. [source]
For `mongod` instances, [`--slowms`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--slowms) affects the diagnostic log and, if enabled, the profiler. [source]
For `mongod` instances, [`--slowOpSampleRate`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#std-option-mongod.--slowOpSampleRate) affects the diagnostic log and, if enabled, the profiler. [source]
Monitor Query Shape Statistics with Query Shape Insights [source]
How Atlas Collects Query Shape Statistics [source]
Total time in microseconds spent running queries with the given query shape. If the query resulted in getMore commands, this metric includes the time spent processing the getMore requests. This metric does not include time spent waiting for the client. [source]
Average time in microseconds spent running queries with the given query shape. If the query resulted in getMore commands, this metric includes the time spent processing the getMore requests. This metric does not include time spent waiting for the client. [source]
Total number of times that queries with the given query shape have been executed. [source]
Total number of documents examined by queries with the given query shape. [source]
Total number of documents returned by queries with the given query shape. [source]
Ratio of documents examined to documents returned by queries with the given query shape. [source]
Execution runtime in microseconds for the most recent query with the given query shape. [source]
Project Owner users can also manually subscribe to or unsubscribe from Query Shape Reports regardless of whether the project has received a query targeting alert. To learn more, see Manage Report Subscriptions. [source]
Name of the collection that the query shape targets. [source]
Average time spent running queries with the given query shape. [source]
Total number of times that queries with the given query shape ran. [source]
Query Shape: In the Search by Query Shape search box, input the query shape or its associated query hash. [source]
Click the checkbox in the row of your desired query shape. [source]
- [Namespace Insights](https://www.mongodb.com/docs/atlas/namespace-insights.md#std-label-namespace-insights) monitors collection-level query latency. [source]
- [Query Shape Reports](https://www.mongodb.com/docs/atlas/query-shape-insights.md#std-label-query-shape-reports), which send a summary of inefficient query shapes to Project Owners by email. [source]
- [Namespace Insights](https://www.mongodb.com/docs/atlas/namespace-insights.md#std-label-namespace-insights), which monitors collection-level query latency. [source]
- The [Query Profiler](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-query-profiler), which describes specific inefficient queries executed on the cluster. [source]
2. Go to the Namespace Insights tab. [source]
Click the Namespace Insights tab. [source]
2. Go to the Query Profiler tab. [source]
Click the Query Profiler tab. [source]
$queryStats collects and reports metrics for aggregate(), find(), distinct(), count(), update(), insert(), deleteOne(), and deleteMany() queries. $queryStats does not collect information for queries that use Queryable Encryption. [source]
The $queryStats stage is enabled on deployments hosted on MongoDB Atlas with a cluster tier of at least M10. [source]
$queryStats must be the first stage in the pipeline. [source]
Specifies additional transformation options for the $queryStats output. [source]
The following sections describe behavioral details of the $queryStats stage. [source]
How $queryStats Groups Returned Documents [source]
$queryStats groups similar queries together by normalizing user-provided field values to their data types. For example, a filter specified as { item: 'card' } is normalized to { item : '?string'}. $queryStats also normalizes the values of some query options like hint and comment. [source]
$queryStats preserves literal values for options like readConcern and readPreference. [source]
For the complete list of options included in a query stats entry, see find Command Query Shape. [source]
How $queryStats Transforms Data Using transformIdentifiers [source]
MongoDB records $queryStats operations in the deployment logs. By default, MongoDB only logs the invocation of $queryStats operations, not the operation's output. For $queryStats operations that include the transformIdentifiers option, you can specify whether the transformed output is included in the log entry. [source]
Each unique combination of attributes creates a separate entry in the $queryStats virtual collection. [source]
A hashed representation of the values in the key. Each unique keyHash value corresponds to a unique entry in the $queryStats memory store. [source]
The fields in key.queryShape vary based on the command that resulted in the query stats entry. $queryStats creates query stats entries for aggregate, find, distinct, count, update, insert, and delete commands. [source]
Each query shape property corresponds to a query option. For example, key.queryShape.sort corresponds to the sort() specification for the query shape. [source]
The following table describes the query shape properties for find commands. [source]
The following table describes the query shape properties for aggregate commands. [source]
The following table describes the query shape properties for distinct commands. [source]
The following table describes the query shape properties for count commands. [source]
The following table describes the query shape properties for update commands. $queryStats tracks query shape statistics for each update statement in the updates array, not for the top-level update command as a whole. [source]
$queryStats does not collect query shape statistics for update operations on time series collections. [source]
The following table describes the query shape properties for insert commands. $queryStats tracks query shape statistics once for the entire insert command, not for each document in the documents array. MongoDB always normalizes documents to a fixed placeholder value, regardless of the number or contents of the inserted documents. [source]
$queryStats does not collect query shape statistics for insert operations on time series collections. [source]
The following table describes the query shape properties for delete commands. $queryStats tracks query shape statistics for each delete statement in the deletes array, not for the top-level delete command as a whole. [source]
$queryStats does not collect query shape statistics for delete operations on time series collections. [source]
The example $queryStats output in the following sections may vary based on the execution of other commands. [source]
const pipeline = [{ $queryStats: {} }]; [source]
MongoDB Atlas periodically uses $queryStats to collect anonymized data about your queries, which helps improve MongoDB products. Your data may also be used to make feature suggestions based on usage. MongoDB retains the data it collects with $queryStats for four years. [source]
| Field | Necessity | Type | Description | | --- | --- | --- | --- | | `transformIdentifiers` | Optional | Document | Specifies additional transformation options for the `$queryStats` output. | | `transformIdentifiers``.algorithm` | Required if specifying the `transformIdentifiers` object | String | The type of hash transformation applied to namespace information and field names in output. The only currently supported `algorithm` value is `hmac-sha-256`. | | `transformIdentifiers``.hmacKey` | Required if specifying the `transformIdentifiers` object | binData | The private key input in the HMAC [source]
- To run `$queryStats` without the `transformIdentifiers` option, the user must have the [`queryStatsRead`](https://www.mongodb.com/docs/manual/reference/privilege-actions.md#mongodb-authaction-queryStatsRead) privilege action. [source]
- To run `$queryStats` with the `transformIdentifiers` option, the user must have the both the [`queryStatsRead`](https://www.mongodb.com/docs/manual/reference/privilege-actions.md#mongodb-authaction-queryStatsRead) and [`queryStatsReadTransformed`](https://www.mongodb.com/docs/manual/reference/privilege-actions.md#mongodb-authaction-queryStatsReadTransformed) privilege actions. [source]
For the complete list of options included in a query stats entry, see [find Command Query Shape.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-find-query-shape) [source]
`$queryStats` does not collect query shape statistics for `update` operations on [time series collections.](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-collection) [source]
`$queryStats` does not collect query shape statistics for `insert` operations on [time series collections.](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-collection) [source]
`$queryStats` does not collect query shape statistics for `delete` operations on [time series collections.](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-collection) [source]
[`db.currentOp()`](https://www.mongodb.com/docs/manual/reference/method/db.currentop.md#mongodb-method-db.currentOp) and the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) report the same basic diagnostic information for all CRUD operations, including the following: [source]
| Field | Type | Necessity | Description | | --- | --- | --- | --- | | `showDebugQueryShape` | boolean | Optional | If `true`, `$querySettings` returns a debugging version of the [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape) output. You'll see an example in the next section. For output details, see [Query Shape.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-queryShape) Default is `false`. | [source]
The database profiler captures data information about read and write operations, cursor operations, and database commands. To configure the database profile and set the thresholds for capturing profile data, see the [Database Profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiler) section. [source]
- [atlas api queryShapeInsights getClusterQueryShape](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-queryshapeinsights-getclusterqueryshape.md#std-label-atlas-api-queryShapeInsights-getClusterQueryShape) - Returns the details for a single query shape. [source]
- [atlas api queryShapeInsights getQueryShapeDetails](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-queryshapeinsights-getqueryshapedetails.md#std-label-atlas-api-queryShapeInsights-getQueryShapeDetails) - Returns the metadata and statistics summary for a given query shape hash. [source]
- [atlas api queryShapeInsights listClusterQueryShapes](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-queryshapeinsights-listclusterqueryshapes.md#std-label-atlas-api-queryShapeInsights-listClusterQueryShapes) - Returns a list of query shapes for one cluster. [source]
- [atlas api queryShapeInsights listQueryShapeSummaries](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-queryshapeinsights-listqueryshapesummaries.md#std-label-atlas-api-queryShapeInsights-listQueryShapeSummaries) - Returns a list of query shape statistics summaries for a given cluster. [source]
- [atlas api queryShapeInsights updateClusterQueryShape](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-queryshapeinsights-updateclusterqueryshape.md#std-label-atlas-api-queryShapeInsights-updateClusterQueryShape) - Updates the rejection status of a query shape. [source]
For commands whose output includes `{ ok: 0 }`, `mongosh` returns a consistent exception and omits the server raw output. The legacy `mongo` shell returns output that varies for each command. [source]
For an example of transformed `$queryStats` output, see [Transformed Example.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-example-transformed) [source]
The following entries show `$queryStats` output for `find`, `aggregate`, `update`, `insert`, and `delete` commands. [source]
Removes cached query plans for a collection. Specify a [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) to remove cached query plans for that shape. Omit the plan cache query shape to clear all cached query plans. [source]
If a collection `orders` has the following plan cache query shape: [source]
| Field | Type | Description | | --- | --- | --- | | `planCacheListFilters` | string | The name of the collection. | | `comment` | any | Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations: [mongod log messages](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-messages-ref), in the `attr.command.cursor.comment` field.; [Database profiler](https://www.mongodb.com/docs/manual/reference/database-profiler.md#std-label-profiler) output, in the [`command.comment`](https://w [source]
The original query shape of the mongos operation may differ after the mongos sends the operation to the mongod . [source]
- The Namespace Insights tab is supported only for Atlas M10+ clusters. [source]
- If you have [Queryable Encryption](https://www.mongodb.com/docs/manual/core/queryable-encryption/) enabled for a collection, Namespace Insights doesn't display the collection in this location. Atlas redacts this information. [source]
- The Namespace Insights tab doesn't support [time series collections.](https://www.mongodb.com/docs/manual/core/timeseries-collections/) [source]
A hexadecimal string that represents the hash of the [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape). For more information, see [Query Shapes.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) [source]
Number of queries executed per hour which match the query shape. [source]
Average execution time in milliseconds for queries which match the query shape. [source]
| Field | Description | | --- | --- | | `"$ownOps"` | Boolean. If set to `true`, returns information on the current user's operations only. On [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instances, users are always authorized to run `currentOp` with `"$ownOps": true` to view their own operations. See [access control.](https://www.mongodb.com/docs/manual/reference/command/currentop.md#std-label-currentOp-access-control) | | `"$all"` | Boolean. If set to `true`, returns information on all operations, including operations on idle connectio [source]
- [operation rejection filters](https://www.mongodb.com/docs/manual/tutorial/operation-rejection-filters.md#std-label-operation-rejection-filters) to block `find`, `aggregation`, and `distinct` commands that have a specified query shape. [source]
- To add query settings for a query shape, use [`setQuerySettings`.](https://www.mongodb.com/docs/manual/reference/command/setquerysettings.md#mongodb-dbcommand-dbcmd.setQuerySettings) [source]
If you provide a query shape to `removeQuerySettings`, include the fields for the existing query settings shape to delete. The field values don't have to match. For example, if you have existing query settings for `find x=1` and provide `find x=100` to `removeQuerySettings`, `removeQuerySettings` deletes the query settings for `find x=1`. [source]
For more information about query shapes, see [Query Shapes.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) [source]
In the following syntax, you provide a query shape hash string in `removeQuerySettings`: [source]
| Field | Type | Necessity | Description | | --- | --- | --- | --- | | `removeQuerySettings` | document or string | Required | You can provide either: The same fields as those in a [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find), [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct), or [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) command, and a `$db` field with the database associated with the original command.; [source]
The Atlas Query Profiler supports binning operations of the same type running on the same collection around the same timestamp to support additional datapoints. [source]
**Important: Please read the following considerations before you enable the Query Profiler.** [source]
The Query Profiler displays approximately 100,000 sampled logs at a time. It shows all the logs if 100,000 or less logs were recorded in the selected hosts and time range. [source]
The Query Profiler does not include bulk operations in its query sampling and analysis. Bulk write operations are excluded from the profiled queries displayed in the Query Profiler. Excluded operations include [`bulkWrite()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.bulkWrite.md#mongodb-method-db.collection.bulkWrite), `updateMany` with multiple `stmtIds`, and similar multi-document operations. [source]
Atlas enables the Query Profiler by default. [source]
2. Disable the Query Profiler. [source]
The Query Profiler displays all your data from the log on the [primary](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-primary) and [secondary](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-secondary) nodes by default. [source]
3. Customize the Query Profiler. [source]
Select the time period from the Time Range or Filter by Date and Time dropdowns. The Query Profiler displays data for the last 24 hours by default. [source]
Click the All operation types dropdown to change which operations you want to profile. The Query Profiler displays all operation types by default. [source]
A hexadecimal string that represents the hash of the query shape. For more information, see Query Shapes. [source]
Namespace Insights monitors collection-level query latency. [source]
Query Shape Reports, which send a summary of inefficient query shapes to Project Owners by email. [source]
Namespace Insights, which monitors collection-level query latency. [source]
The Query Profiler, which describes specific inefficient queries executed on the cluster. [source]
Returns a list of query shapes for one cluster. [source]
Query shapes may be filtered by their status; at present, this endpoint supports only the REJECTED status. [source]
The Query Profiler displays all your data from the log on the primary and secondary nodes by default. [source]
Click View more details in the right-side panel to view the full query shape, detailed execution statistics, query log, and suggestions for improvement. [source]

Related concepts

Query Insights — is a part of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
index — is a related of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
query optimization — is a hypernym of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
Atlas Search vs text search — is a contrast of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
Compass explain — is a part of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
release notes — is a part of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor
AI-assisted query optimization — is a part of MongoDB query optimization tooling: Compass explain, Atlas Query Insights and Performance Advisor

Children

Atlas Search index sync lag and search-node sizing
Atlas Search regex and wildcard operator cost
Collation-based case-insensitive indexes as a $regex alternative (frontier, viability 2.80 / 5, below research gate)
Database Profiler vs Query Profiler
Explain verbosity modes
MongoDB MCP Server read-only and permission model
Namespace Insights (frontier)
Natural language to MongoDB query generation
Validating AI-suggested indexes with explain
Atlas Slow-Query Log and Performance Advisor Automation

Frontier under this node: Collation-based case-insensitive indexes as a $regex alternative (2.80), Namespace Insights

← the whole tree · 3D view· how to read this page