> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt # setQuerySettings (database command) ## Definition **New in version 8.0** `setQuerySettings` defines query settings used by the [`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), and [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) commands. 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. 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. Cluster query settings take precedence over query settings or index hints passed as a command field. MongoDB ignores index hints in command fields if a matching query setting already contains index hints. Index hints don't affect [query shape.](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) For more information about hints and query settings, see [Query Settings Syntax.](https://www.mongodb.com/docs/manual/reference/command/setquerysettings.md#std-label-setQuerySettings-syntax) **Note:** To remove query settings, use [`removeQuerySettings`](https://www.mongodb.com/docs/manual/reference/command/removequerysettings.md#mongodb-dbcommand-dbcmd.removeQuerySettings). To see current query settings, use a [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) stage in an aggregation pipeline. ### Query Settings and Index Filters Starting in MongoDB 8.0, [index filters](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) are deprecated. Use query settings instead. Query settings have more functionality than index filters. Index filters aren't persistent, and you can't easily create index filters for all cluster nodes. ## Compatibility This command is available in deployments hosted in the following environments: - [MongoDB Atlas](https://www.mongodb.com/docs/atlas): The fully managed service for MongoDB deployments in the cloud **Important:** This command is not supported in M0 and Flex clusters. For more information, see [Unsupported Commands.](https://www.mongodb.com/docs/atlas/unsupported-commands/) - [MongoDB Enterprise](https://www.mongodb.com/docs/manual/administration/install-enterprise.md#std-label-install-mdb-enterprise): The subscription-based, self-managed version of MongoDB - [MongoDB Community](https://www.mongodb.com/docs/manual/administration/install-community.md#std-label-install-mdb-community-edition): The source-available, free-to-use, and self-managed version of MongoDB ## Syntax You can add or update query settings using either of the two syntax specifications shown in this section. ### Set Query Settings by Passing in a Query In the following syntax, you provide: - 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 `setQuerySettings`. - A `$db` field to specify the database for the query settings. - A `settings` document with `indexHints` and other fields. ```javascript db.adminCommand( { setQuerySettings: { , // Provide fields for // find, distinct, or aggregate command $db: // Provide a database name }, // Provide a settings document with indexHints and other fields settings: { indexHints: [ { ns: { db: , coll: }, allowedIndexes: }, ... ], queryFramework: , reject: , comment: , queryKnobs: , maxTimeMS: } } ) ``` ### Set Query Settings by Passing in a Query Shape Hash You can provide an existing query shape hash string in `setQuerySettings` and an updated `settings` document with `indexHints` and other fields: ```javascript db.adminCommand( { setQuerySettings: , // Provide an existing query shape hash string // Provide a settings document with indexHints and other fields settings: { indexHints: [ { ns: { db: , coll: }, allowedIndexes: }, ... ], queryFramework: , reject: , comment: , queryKnobs: , maxTimeMS: } } ) ``` A query shape hash is a string that uniquely identifies the query shape. An example query shape hash is `"F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"`. 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) To obtain the query shape hash string, do any of these: - 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. - Examine the [database profiler](https://www.mongodb.com/docs/manual/tutorial/manage-the-database-profiler.md#std-label-database-profiling) output. - View the [slow query logs.](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) If you set the query settings using a hash string, the `representativeQuery` field is initially absent from the `$querySettings` aggregation stage output. 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. 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. To hold the additional representative queries, MongoDB 8.3 also increases the storage capacity for representative queries in query settings beyond the original 16 MB BSON document limit. **Tip:** In both syntax variations, you can provide an array of `indexHints` documents. You can omit the array brackets if you provide only one `indexHints` document. ## Command Fields The `settings` document in the `setQuerySettings` command takes the following fields: | 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.; An existing query shape hash string that uniquely identifies the query shape. An example query shape hash is `"F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"``. | | `indexHints.ns` | document | Optional | Namespace for index hints. Only required when optional index hints are specified. | | `indexHints.ns.db` | string | Conditional | Name of the database for the index hints. Required when you specify `indexHints.ns`. | | `indexHints.ns.coll` | string | Conditional | Name of the collection for the index hints. Required when you specify `indexHints.ns`. | | `indexHints.allowedIndexes` | array | Optional | Array of indexes for index hints. An index hint can be one of these: Index name; index key pattern; [`$natural`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-operator-metaOp.-natural) hint For more details, see [Indexes](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) and [`hint()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) | | `queryFramework` | string | Optional | [Query framework](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-framework) string can be set to: `classic` to use the classic engine.; `sbe` to use slot-based query execution engine. For details, see [Slot-Based Query Execution Engine.](https://www.mongodb.com/docs/manual/reference/sbe.md#std-label-sbe-landing) | | `reject` | boolean | Optional | If `true`: New queries with the matching query shape are rejected and the query response states the query is rejected.; Any queries currently in progress aren't rejected. Default is `false`. To enable a query shape, run `setQuerySettings` again for the query shape and set `reject` to `false`. If you set `reject` to `true` and then back to `false` using `setQuerySettings` then: If your `settings` document isn't empty, then `setQuerySettings` enables the query shape.; If your `settings` document only contains `reject: false`, then `setQuerySettings` returns an error. Instead, use the `removeQuerySettings` command to remove the settings and then use `setQuerySettings` to add query settings. | | `comment` | BSON type | Optional | A comment can be any valid [BSON type](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-bson-types). For example: string, object, and so on. You can use a comment to provide additional information about the query settings. For example, to add a string that indicates why you added the query settings, use `comment: "Index hint for orderDate_1 index to improve query performance"`. To update a comment, run `setQuerySettings` again and use `comment: { body: { msg: "Updated comment" } }`. You cannot remove a comment, but you can set it to a string with a space character. You can remove the query settings using [`removeQuerySettings`.](https://www.mongodb.com/docs/manual/reference/command/removequerysettings.md#mongodb-dbcommand-dbcmd.removeQuerySettings) Comments appear in the [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) aggregation pipeline stage output, the [explain() command output](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results), and the [slow query logs.](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-message-slow-ops) **New in version 8.1** (and 8.0.4). | | `queryKnobs` | document | Optional | A document of `{ : }` pairs that override internal server parameters for this query shape only, instead of instance-wide with [`setParameter`](https://www.mongodb.com/docs/manual/reference/command/setparameter.md#mongodb-dbcommand-dbcmd.setParameter). Each knob keeps the type, bounds, and default of its underlying server parameter. Unlike the other `setQuerySettings` fields, which replace the entire value when set, `queryKnobs` merges with existing knob values. `setQuerySettings` changes the knobs you include in the `queryKnobs` document, and leaves all other knobs unchanged. `queryKnobs: {}` results in a no-op. To remove a single knob without affecting the others, set that knob's value to `null`. **New in version 9.0** | | `maxTimeMS` | non-negative integer | Optional | Sets a time limit in milliseconds for executions of the [query shape](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes). Use this setting to cap a single regressed shape or lift an over-tight client timeout without changing application code. If you omit `maxTimeMS` from query settings, the operation's timeout follows the command-level `maxTimeMS` option and the [`defaultMaxTimeMS`](https://www.mongodb.com/docs/manual/reference/cluster-parameters/defaultmaxtimems.md#mongodb-parameter-param.defaultMaxTimeMS) cluster parameter. Setting `maxTimeMS` to `0` clears the `maxTimeMS` query setting, and the operation falls back to the command-level and cluster default values. A `maxTimeMS` value set with `setQuerySettings` takes precedence over a `maxTimeMS` value supplied in the command. **New in version 9.0** | ### Query Knobs **New in version 9.0** 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. 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 }`. Each knob keeps the type, bounds, and default of its underlying server parameter, and `setQuerySettings` runs the same validation that [`setParameter`](https://www.mongodb.com/docs/manual/reference/command/setparameter.md#mongodb-dbcommand-dbcmd.setParameter) runs. `setQuerySettings` rejects the following values: - Unknown knobs - Knobs that are not marked as settable through query settings - Wrong BSON types - Invalid enum strings - Out-of-range values - Knobs whose minimum FCV requirement exceeds the cluster FCV The effective value of a knob follows this precedence, from highest to lowest: 1. Per-shape value set with `queryKnobs` 2. Instance-wide value set with [`setParameter`](https://www.mongodb.com/docs/manual/reference/command/setparameter.md#mongodb-dbcommand-dbcmd.setParameter) 3. Compiled-in default value Query knobs are part of the hashed query settings, so they are also part of the [plan cache key](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-plan-cache-key). If you change a knob value, MongoDB produces a new plan cache key, so the planner creates a fresh plan instead of reusing a stale cached plan. 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. **Important:** When you downgrade the FCV from `9.0` to an earlier version, a migration updates the stored query settings. The migration removes every knob whose minimum FCV exceeds the target version and deletes any settings entry that still contains only default values. Re-upgrading to `9.0` does not restore the removed knobs. You must re-apply them with `setQuerySettings`. ## Examples 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. 1. Create the example collection and indexes Run: ```javascript // Create pizzaOrders collection db.pizzaOrders.insertMany( [ { _id: 0, type: "pepperoni", size: "small", price: 19, totalNumber: 10, orderDate: ISODate( "2023-03-13T08:14:30Z" ) }, { _id: 1, type: "pepperoni", size: "medium", price: 20, totalNumber: 20, orderDate: ISODate( "2023-03-13T09:13:24Z" ) }, { _id: 2, type: "pepperoni", size: "large", price: 21, totalNumber: 30, orderDate: ISODate( "2023-03-17T09:22:12Z" ) }, { _id: 3, type: "cheese", size: "small", price: 12, totalNumber: 15, orderDate: ISODate( "2023-03-13T11:21:39.736Z" ) }, { _id: 4, type: "cheese", size: "medium", price: 13, totalNumber: 50, orderDate: ISODate( "2024-01-12T21:23:13.331Z" ) }, { _id: 5, type: "cheese", size: "large", price: 14, totalNumber: 10, orderDate: ISODate( "2024-01-12T05:08:13Z" ) }, { _id: 6, type: "vegan", size: "small", price: 17, totalNumber: 10, orderDate: ISODate( "2023-01-13T05:08:13Z" ) }, { _id: 7, type: "vegan", size: "medium", price: 18, totalNumber: 10, orderDate: ISODate( "2023-01-13T05:10:13Z" ) } ] ) // Create ascending index on orderDate field db.pizzaOrders.createIndex( { orderDate: 1 } ) // Create ascending index on totalNumber field db.pizzaOrders.createIndex( { totalNumber: 1 } ) ``` The indexes have the default names `orderDate_1` and `totalNumber_1`. 2. Add query settings for a find command The following example adds query settings for a [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find) command. The example provides fields in `setQuerySettings` for the `find` command, and includes the `orderDate_1` index in `allowedIndexes`. ```javascript db.adminCommand( { setQuerySettings: { find: "pizzaOrders", filter: { orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) } }, sort: { totalNumber: 1 }, $db: "test" }, settings: { indexHints: { ns: { db: "test", coll: "pizzaOrders" }, allowedIndexes: [ "orderDate_1" ] }, queryFramework: "classic", comment: "Index hint for orderDate_1 index to improve query performance" } } ) ``` 3. (Optional) Verify the query settings Run this [`explain`](https://www.mongodb.com/docs/manual/reference/command/explain.md#mongodb-dbcommand-dbcmd.explain) command: ```javascript db.pizzaOrders.explain().find( { orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } ) ``` The following truncated output shows the query settings are set: ```javascript queryPlanner: { winningPlan: { stage: 'SINGLE_SHARD', shards: [ { explainVersion: '1', ... namespace: 'test.pizzaOrders', indexFilterSet: false, parsedQuery: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } }, querySettings: { indexHints: { ns: { db: 'test', coll: 'pizzaOrders' }, allowedIndexes: [ 'orderDate_1' ] }, queryFramework: 'classic', comment: 'Index hint for orderDate_1 index to improve query performance' }, ... } ... ] } } ``` 4. (Optional) Run the query The following example runs the query: ```javascript db.pizzaOrders.find( { orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } ) ``` The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query. Query output: ```javascript [ { _id: 0, type: 'pepperoni', size: 'small', price: 19, totalNumber: 10, orderDate: ISODate('2023-03-13T08:14:30.000Z') }, { _id: 5, type: 'cheese', size: 'large', price: 14, totalNumber: 10, orderDate: ISODate('2024-01-12T05:08:13.000Z') }, { _id: 3, type: 'cheese', size: 'small', price: 12, totalNumber: 15, orderDate: ISODate('2023-03-13T11:21:39.736Z') }, { _id: 1, type: 'pepperoni', size: 'medium', price: 20, totalNumber: 20, orderDate: ISODate('2023-03-13T09:13:24.000Z') }, { _id: 2, type: 'pepperoni', size: 'large', price: 21, totalNumber: 30, orderDate: ISODate('2023-03-17T09:22:12.000Z') }, { _id: 4, type: 'cheese', size: 'medium', price: 13, totalNumber: 50, orderDate: ISODate('2024-01-12T21:23:13.331Z') } ] ``` 5. (Optional) Obtain the query settings The following example uses a [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) stage in an aggregation pipeline to obtain the query settings: ```javascript db.aggregate( [ { $querySettings: {} } ] ) ``` Truncated output, which includes the `queryShapeHash` field: ```javascript [ { queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1', settings: { indexHints: [ { ns: { db: 'test', coll: 'pizzaOrders' }, allowedIndexes: [ 'orderDate_1' ] } ], queryFramework: 'classic', comment: 'Index hint for orderDate_1 index to improve query performance' }, representativeQuery: { find: 'pizzaOrders', filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } }, sort: { totalNumber: 1 }, '$db': 'test' } } ] ``` 6. Add query settings for a distinct command The following example adds query settings for a [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command: ```javascript db.adminCommand( { setQuerySettings: { distinct: "pizzaOrders", key: "totalNumber", query: { totalNumber: 10, orderDate :{ '$gt': ISODate('2023-01-20T00:00:00.000Z') } } , $db: "test" }, settings: { indexHints: { ns: { db: "test", coll: "pizzaOrders" }, allowedIndexes: [ "orderDate_1" ] }, queryFramework: "classic", comment: "Index hint for orderDate_1 index to improve query performance" } } ) ``` 7. Add query settings for an aggregate command The following example adds query settings for an [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) command: ```javascript db.adminCommand( { setQuerySettings: { aggregate: "pizzaOrders", pipeline: [ { $match: { totalNumber: 10, orderDate :{ '$gt': ISODate('2023-01-20T00:00:00.000Z') } } }, { $group: { _id: "$type", totalMediumPizzaOrdersGroupedByType: { $sum: "$totalNumber" } } } ], $db: "test" }, settings: { indexHints: { ns: { db: "test", coll: "pizzaOrders" }, allowedIndexes: [ "totalNumber_1" ] }, queryFramework: "classic", comment: "Index hint for totalNumber_1 index to improve query performance" } } ) ``` ## Learn More - [Query Plans](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plans-query-optimization) - [Indexes](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) - [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querysettings.md#mongodb-pipeline-pipe.-querySettings) - [`removeQuerySettings`](https://www.mongodb.com/docs/manual/reference/command/removequerysettings.md#mongodb-dbcommand-dbcmd.removeQuerySettings) - [Aggregation Pipeline](https://www.mongodb.com/docs/manual/core/aggregation-pipeline.md#std-label-aggregation-pipeline) - [Query Shapes](https://www.mongodb.com/docs/manual/core/query-shapes.md#std-label-query-shapes) - [Query statistics for query shapes](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-queryShape) - [Block Slow Queries with Operation Rejection Filters](https://www.mongodb.com/docs/manual/tutorial/operation-rejection-filters.md#std-label-operation-rejection-filters)