The new query planner for Amazon DocumentDB (planner version 2.0) features advanced query optimization capabilities and improved performance. Planner version 2.0 for Amazon DocumentDB 5.0 delivers up to 10x performance improvement over the prior version when using find and update operators with indexes. Performance improvements primarily come from using more optimal index plans and enabling index scan support for operators such as negation operators ($neq, $nin) and nested $elementMatch. Planner version 2.0 queries run faster through better cost estimation techniques, optimized algorithms, and enhanced stability. Planner version 2.0 supports plan cache filter APIs as well, which enhances planner stability. With this feature, Amazon DocumentDB 5.0 now offers the ability to select from the different versions of query planner.

Topics

Prerequisites

Selecting planner version 2.0 as the default query planner

Best practices

Limitations

Improvements to Find and Update Operators

Plan cache filter API

Potential behavior differences between planner version 1.0, 2.0, and MongoDB

Planner version 2.0 bridges behavior gap with MongoDB

Prerequisites

The following prerequisites apply to planner version 2.0:

Planner version 2.0 is available in all regions where engine version 5.0 is available.

To opt for using version 2.0 as the default query planner, your cluster needs to be on engine patch version 3.0.15902 or later of Amazon DocumentDB version 5.0. For steps to update to the latest engine version patch, see Performing a patch update to a cluster’s engine version.

To set planner version 2.0 as the default query planner, you need IAM permissions to update cluster parameter groups.

Selecting planner version 2.0 as the default query planner

Use the following steps to select 2.0 as the default query planner from the console or CLI:

Follow the steps in Modifying Amazon DocumentDB cluster parameters to modify your cluster’s parameter group.

For the parameter titled ‘plannerVersion’, change the value to 2.0 indicating planner version 2.0.

Select Apply immediately (selecting Apply at reboot will render the selection ineffective until next reboot of the cluster).

Best practices

For expected results, use the following best practices when applying planner version 2.0:

In a global cluster, select the same plannerVersion value (1.0 or 2.0) in the cluster parameter groups for both regions. Note that selecting different planner versions in primary and secondary regions may cause inconsistent query behavior and performance.

Updating to planner version 2.0 during a scheduled maintenance windows or during reduced traffic periods will be the least disruptive, as there may be increased error rates if the planner version is changed when workloads are actively running.

Planner version 2.0 works most optimally with MongoDB shell version 5.0.

Limitations

The following limitations apply to planner version 2.0:

Planner version 2.0 is not supported in elastic clusters, which will fall back to planner version 1.0.

Planner version 2.0 is not supported for aggregation and distinct commands, which will fall back to planner version 1.0.

Queries that contain regex, text search, geospatial, jsonschema or $expr in filters are not supported with plan cache filter in planner version 2.0.

Improvements to Find and Update Operators

Planner version 2.0 optimizes fundamental operations including find, update, delete, and find-and-modify commands. The following tabbed sections show enhanced capabilities for indexes, as well as the query performance improvements with planner version 2.0:

Plan cache filter API

Note

Text index is not supported with plan cache filter.

Planner version 2.0 adds support for the index filter feature that allows you to specify a list of indexes that a specific query shape can use. This feature is accessible through the API and can be controlled from the server side. If you experience a query regression, this feature gives you a faster and more flexible option to mitigate the issue without having to modify your application code.

db.runCommand({ planCacheSetFilter: <collection>, query: <query>,
sort: <sort>, // optional,
indexes: [ <index1>, <index2>, ...],
comment: <any> // optional})

To list all filters on the collection, use the following command:

db.runCommand(
{
planCacheListFilters: <collection>
}
)

This command shows all index filters on the collection. Example output:

{
"filters" : [
{
"query" : {a: "@", b: "@"},
"sort" : {a: 1},
"indexes" : [
<index1>,
...
]
},
...
],
"ok": 1
}

You can use two new fields from the explain command output to analyze planner version 2.0’s index filtering: indexFilterSet and indexFilterApplied. indexFilterSet is set to “true” if there’s an index filter set on the collection that matches the query shape. indexFilterApplied is set to “true” if, and only if the query applied index filter and chose a plan using an index in the filter list.

You can clear the index filter with the following command:

db.runCommand(
{
planCacheClearFilters: <collection>>
query: <query pattern>, // optional
sort: <sort specification>, // optional
comment: <any>. //optional
}
)

To clear all filters on collection “foo”, use the following command:

db.runCommand({planCacheClearFilters: "foo"})

To clear a specific query shape with any sort, you can copy and paste the query shape from the output of planCacheListFilters:

db.runCommand({planCacheClearFilters: "foo", query: {a: @}})

To clear a specific query shape with a specified field to sort by, you can copy and paste the query shape from the output of planCacheListFilters:

db.runCommand({planCacheClearFilters: "foo", query: {a: @},sort: {a: 1}})

Potential behavior differences between planner version 1.0, 2.0, and MongoDB

In some edge cases, it is possible that planner version 2.0 may produce results that slightly vary from results in MongoDB. This section walks through some examples of these possibilities.

Planner version 2.0 bridges behavior gap with MongoDB

There are some areas where planner version 2.0 closes the behavior gaps from MongoDB:

Planner version 2.0 allows numeric index lookup on flattened arrays for $elemMatch:

doc: {"x" : [ [ { "y" : 1 } ] ] }

// Planner Version 2 and mongo
> db.bar.find({"x.0": {$elemMatch: {y: 1}}})
{ "_id" : ObjectId("68192947945e5846634c455a"), "x" : [ [ { "y" : 1 } ] ] }
> db.bar.find({"x": {$elemMatch: {"0.y": 1}}})
{ "_id" : ObjectId("68192947945e5846634c455a"), "x" : [ [ { "y" : 1 } ] ] }

//Whereas Planner Version 1 wouldn't return any results.
> db.bar.find({"x.0": {$elemMatch: {y: 1}}})
> db.bar.find({"x": {$elemMatch: {"0.y": 1}}})

While planner version 1.0 excluded strings in projection, planner version 2.0’s behavior aligns with MongoDB and treats them as literal values“

// Planner V2/ MongoDB
> db.col.find()
{ "_id" : ObjectId("681537738aa101903ed2fe05"), "x" : 1, "y" : 1 }
> db.col.find({},{x:"string"})
{ "_id" : ObjectId("681537738aa101903ed2fe05"), "x" : "string" }

// Planner V1 treats strings as exclude in projection
rs0:PRIMARY> db.col.find()
{ "_id" : ObjectId("68153744d42969f11d5cca72"), "x" : 1, "y" : 1 }
rs0:PRIMARY> db.col.find({},{x:"string"})
{ "_id" : ObjectId("68153744d42969f11d5cca72"), "y" : 1 }

Planner version 2.0, like MongoDB, does not allow projection on same fields “x” and “x.a”:

// Planner version 2/MongoDB will error out
> db.col.find()
{ "_id" : ObjectId("68153da2012265816bc9ba23"), "x" : [ { "a" : 1 }, 3 ] }
db.col.find({},{"x.a":1,"x":1}) // error

// Planner Version 1 does not error out
db.col.find()
{ "_id" : ObjectId("68153da2012265816bc9ba23"), "x" : [ { "a" : 1 }, 3 ] }

db.col.find({},{"x.a":1,"x":1})
{ "_id" : ObjectId("68153d60143af947c720d099"), "x" : [ { "a" : 1 }, 3 ] }

Planner version 2.0, like MongoDB, allows projection on subdocuments:

// Planner Version2/MongoDB supports projections on subdocuments
db.col.find()
{ "_id" : ObjectId("681542d8f35ace71f0a50004"), "x" : [ { "y" : 100 } ] }
> db.col.find({},{"x":{"y":1}})
{ "_id" : ObjectId("681542b7a22d548e4ac9ddea"), "x" : [ { "y" : 100 } ] }

// Planner V1 throws error if projection is subdocument
db.col.find()
{ "_id" : ObjectId("681542d8f35ace71f0a50004"), "x" : [ { "y" : 100 } ] }
rs0:PRIMARY> db.col.find({},{"x":{"y":1}})
Error: error: {
"ok" : 0,
"operationTime" : Timestamp(1746223914, 1),
"code" : 2,
"errmsg" : "Unknown projection operator y"
}

With Planner version 2.0, like MongoDB, projection does not support fields after the $ operator:

// Mongo and Planner Version 2 will error out
db.col.find()
{ "_id" : ObjectId("68155fa812f843439b593f3f"), "x" : [ { "a" : 100 } ] }
db.col.find({"x.a":100},{"x.$.a":1}) - // error

// v1 will not error out
db.col.find()
{ "_id" : ObjectId("68155fa812f843439b593f3f"), "x" : [ { "a" : 100 } ] }
db.col.find({"x.a":100},{"x.$.a":1})
{ "_id" : ObjectId("68155dee13b051d58239cd0a"), "x" : [ { "a" : 100 } ] }

Planner version 2.0, like MongoDB, allows $hint usage:

// v1 will error out on $hint if there are no filters
db.col.find({}).hint("x_1")
Error: error: {
"ok" : 0,
"operationTime" : Timestamp(1746466616, 1),
"code" : 2,
"errmsg" : "Cannot use Hint for this Query. Index is multi key index , partial index or sparse index and query is not optimized to use this index."
}


// Mongo and Planner Version 2 will allow $hint usage
db.col.find({}).hint("x_1")
{ "_id" : ObjectId("6818f790d5ba9359d68169cf"), "x" : 1 }