setQuerySettings (database command)

Definition

setQuerySettings

New in version 8.0. :

setQuerySettings defines query settings used by the find, distinct, and aggregate commands.

You can use query settings to add index hints, define operation rejection filters, and set other fields for all executions of a given query shape on a cluster. A cluster's query settings persist across restarts.

The 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.

For more information about hints and query settings, see Query Settings Syntax.

Note

To remove query settings, use removeQuerySettings. To see current query settings, use a $querySettings stage in an aggregation pipeline.

Query Settings and Index Filters

Starting in MongoDB 8.0, 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.

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, distinct, or 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.

db.adminCommand( {
   setQuerySettings: {
      <fields>,  // Provide fields for
                 // find, distinct, or aggregate command
      $db: <string>  // Provide a database name
   },
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>,
      comment: <BSON type>
   }
} )

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:

db.adminCommand( {
   setQuerySettings: <string>,  // Provide an existing query shape hash string
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>,
      comment: <BSON type>
   }
} )

A query shape hash is a string that uniquely identifies the query shape. An example query shape hash is "F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C".

To obtain the query shape hash string, do any of these:

Use a $querySettings stage in an aggregation pipeline and examine the queryShapeHash field.
Examine the database profiler output.
View the slow query logs.

If you set the query settings using a hash string, then you won't have the representativeQuery field in the $querySettings aggregation stage output.

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 command takes these fields:

Field

Field Type

Necessity

Description

setQuerySettings

document or string

Required

You can provide either:

The same fields as those in a find, distinct, or 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.

`db`	string	Required	Name of the database for index hints.
`coll`	string	Required	Name of the collection for index hints.

indexHints.allowedIndexes

array

Optional

Array of indexes for index hints. An index hint can be one of these:

Index name
index key pattern
$natural hint

For more details, see Indexes and hint().

queryFramework

string

Optional

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.

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. 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.

Comments appear in the $querySettings aggregation pipeline stage output, the explain() command output, and the slow query logs.

Available starting in MongoDB 8.0.4.

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.

Create the example collection and indexes

Run:

 // 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.

Add query settings for a find command

The following example adds query settings for a find command. The example provides fields in setQuerySettings for the find command, and includes the orderDate_1 index in allowedIndexes.

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"
   }
} )

(Optional) Verify the query settings

Run this explain command:

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:

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'
         },
         ...
       }
     ...
     ]
   }
}

(Optional) Run the query

The following example runs the query:

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:

[
   {
      _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')
   }
]

(Optional) Obtain the query settings

The following example uses a $querySettings stage in an aggregation pipeline to obtain the query settings:

db.aggregate( [
   { $querySettings: {} }
] )

Truncated output, which includes the queryShapeHash field:

[
   {
      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'
      }
   }
]

Add query settings for a distinct command

The following example adds query settings for a distinct command:

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"
   }
} )

Add query settings for an aggregate command

The following example adds query settings for an aggregate command:

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

Back

setDefaultRWConcern

setUserWriteBlockMode

setQuerySettings (database command)

Definition

Note

Query Settings and Index Filters

Syntax

Set Query Settings by Passing in a Query

Set Query Settings by Passing in a Query Shape Hash

Tip

Command Fields

Examples

Create the example collection and indexes

Add query settings for a find command

(Optional) Verify the query settings

(Optional) Run the query

(Optional) Obtain the query settings

Add query settings for a distinct command

Add query settings for an aggregate command

Learn More

Earn a Skill Badge