Database Manual / Reference / Database Commands / Administration

setQuerySettings (database command)

Definition

setQuerySettings

New in version 8.0.

You can use query settings to add index hints, define operation rejection filters, and set other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown.

For example, query settings allow you to use an index for all executions of a query shape in a cluster.

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query.

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

Starting in MongoDB 8.0, use query settings instead of index filters. Index filters are deprecated starting in MongoDB 8.0.

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.

Note

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

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:

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:

FieldField TypeNecessityDescription

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:

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.1 (and 8.0.4).

Examples

The following examples create a collection and add query settings for different commands. The examples use one index for all executions of a query shape run in the cluster.

1

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.

2

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

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

(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')
   }
]
5

(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'
      }
   }
]
6

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: { type: "pepperoni"} ,
      $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 command:

db.adminCommand( {
   setQuerySettings: {
      aggregate: "pizzaOrders",
      pipeline: [
         { $match: { size: "medium" } },
         { $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

Earn a Skill Badge

Master "Query Optimization" for free!

Learn more