Amazon DocumentDB (with MongoDB compatibility) introduces new query planner that delivers up to 10x performance improvements

On Oct 28, 2025, Amazon DocumentDB (with MongoDB compatibility) introduced a new query planner (NQP) to improve database performance and stability. The redesigned architecture uses improved cost estimation techniques and optimized algorithms for smarter query plan selection. For queries using operators such as "$nin", "$ne", “$not” and nested "$elemMatch", users can expect up to tenfold performance boost when using indexes.

The NQP currently focuses on optimizing fundamental operations including find, update, delete, and find-and-modify commands. This post covers enhancements to these core operations.

Amazon DocumentDB (with MongoDB compatibility) is a serverless, fully managed native JSON document database that makes it straightforward and cost effective to operate critical document workloads on virtually any scale without managing infrastructure. Amazon DocumentDB simplifies your architecture by providing built-in security best practices, continual backups, and built-in integrations with other AWS services. You can enhance your applications with generative AI and machine learning (ML) capabilities using vector search for Amazon DocumentDB and integration with Amazon SageMaker Canvas. As a document database, Amazon DocumentDB makes it straightforward to store, query, and index JSON data.

Prerequisites to enable New Query Planner

To use the new query planner, you need the following prerequisites:

1. To choose the new query planner as the default query planner, your cluster needs to be on the Engine Patch Version 3.0.15902 or later of Amazon DocumentDB version 5.0. For steps to update to the latest patch, visit Performing a patch update to a cluster’s engine version.
2. The new query planner is available in all Regions where engine version 5.0 is available.
3. To set the new query planner as the default query planner, you need AWS Identity and Access Management (IAM) permissions to update cluster parameter groups.

Getting started

To select the new query planner as the default query planner from the AWS Management Console or AWS Command Line Interface (AWS CLI), follow these steps:

1. To modify your cluster’s parameter group, refer to the steps at Modifying Amazon DocumentDB cluster parameters in the Amazon DocumentDB Developer Guide.
2. For the parameter titled planner_version, change the value to 2.0, indicating planner version 2.0. You also need to have the cluster using custom parameter group.
3. Choose Apply immediately.
4. Change to cluster parameter is dynamic change which doesn’t require reboot. However, when workloads are actively running, changing the planner version might cancel running queries. We recommend updating the planner version during maintenance windows or periods of reduced traffic.

If you want to check that the optimizer is picking the new query planner, you can check the planner version with explain() output with plannerVersion as 2 (PlannerVersion 2.0):

rs0 [direct: primary] test> db.foo.find({"name":"bar"}).explain()
{
  queryPlanner: {
    plannerVersion: 2,
    namespace: 'test.foo',
    winningPlan: { stage: 'COLLSCAN', filter: { name: { '$eq': 'bar' } } }
  },
  indexFilterSet: false,
  indexFilterApplied: false,
  serverInfo: { host: 'nqp-preview-cluster', port: 27017, version: '5.0.0' },
  ok: 1,
  operationTime: Timestamp({ t: 1754382670, i: 1 })
}

Let’s dive into the key features and improvements NQP brings.

Enhanced index support

The NQP introduces expanded index support for several operators that previously couldn’t use indexes effectively. This expanded support will deliver substantial performance improvements.

NQP will include index scan support for negation operators $nin, $ne, $not{$eq}and$not{$in}.

Sample Document: { "x": 10, "y": [1, 2, 3] }
db.foo.createIndex({ "x": 1, "y": 1 })
db.foo.find({ "x": {$nin: [20, 30] }})

Query Plan – Planner Version 1.0:

  queryPlanner: {
    plannerVersion: 1,
    namespace: 'nqptest.foo',
    winningPlan: { stage: 'COLLSCAN' }
  }

Query Plan – Planner Version 2.0:

queryPlanner: {
    plannerVersion: 2,
    namespace: 'nqptest.foo',
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        indexName: 'x_1_y_1',
        indexCond: {
          '$and': [
            { x: { '$nin': [ 20, 30 ] } }
          ]
        }
      }
    }
  }

NQP supports index scan support for $type and nested $elemMatch:

Sample Document: {"x": true, "y": [7, 8, 9]} 
db.foo.createIndex({ "x": 1, "y": 1 }) 
db.foo.find({"x":{ $type: "string" }})

Sample Document: {"x": [{"y": [1, 2, 5]}]} 
db.foo.createIndex({"x.y": 1}) 
db.foo.find({"x":{$elemMatch:{"y":{$elemMatch:{"$gt": 3 }}}}})

NQP will use sparse or partial indexes even when $exists is not present in the query expression, eliminating the need for $exists:

Sample Document: {"name": "Bob", "email": "example@fake.com" } 
db.foo.createIndex({"email":1},{partialFilterExpression:{"email":{$exists: true}}})

While using the existing planner (Planner Version 1.0), you need to specify the $exists clause to pick up the partial index with query. With the new query planner (Planner Version 2.0) , you don’t need to do it.

Planner Version 1.0:

db.foo.find({email: "example@fake.com", email: {$exists: true}})

Planner Version 2.0 (without $exists clause):

db.foo.find({ email: "example@fake.com" })

queryPlanner: {
	plannerVersion: 2,
	namespace: 'nqptest.foo',
	winningPlan: {
		stage: 'IXSCAN',
		indexName: 'email_1',
		direction: 'forward',
		indexCond: {
			'$and': [ { email: { '$eq': 'example@fake.com' } } ]
	}
}

Without New Query Planner, an index was used only when conditions were the same as the partial index filter. Now, the index is used even when the query conditions don’t satisfy the index criteria and cover index:

Sample Document: {"name": "Bob", "age": 34}
db.foo.createIndex({"age":1},{partialFilterExpression:{"age":{$lt:50}}}) 

The following query will use indexes with Planner Version 1.0:

db.foo.find({"age":{$lt:50}})

The following query will not use index and will result in following error with Planner Version 1.0.

db.foo.find({"age":{$lt:30}})

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

The following query will work with Planner Version 2.0.

db.foo.find({"age":{$lt:30}})

Query Plan – Planner Version 2.0:

queryPlanner: {
	plannerVersion: 2, 
	namespace: 'nqptest.foo', 
	winningPlan: { 
		stage: 'FETCH', 
		inputStage: { 
			stage: 'IXSCAN', 
			indexName: 'age_1', 
			indexCond: { 
				'$and': [ { age: { '$lt': 30 } } ] 
			} 
		} 
	}

Using the new query planner, you are now be able to use partial index with $elemMatch queries:

Sample Document: {"name": "Bob", "age": [34,35,36]} 
db.foo.createIndex({"age":1},{partialFilterExpression:{"age": {$lt:50,$gt:20}}}) 
db.foo.find({age:{$elemMatch:{$lt:50,$gt:20}}})

Query Plan – Planner Version 1.0:

queryPlanner: { 
	plannerVersion: 1, 
	namespace: 'nqptest.foo', 
	winningPlan: { 
		stage: 'COLLSCAN' 
		} 
	}

Query Plan – Planner Version 2.0:

queryPlanner: { 
	plannerVersion: 2, 
	namespace: 'nqptest.foo', 
	winningPlan: { 
		stage: 'FETCH', 
		inputStage: { 
			stage: 'IXSCAN', 
			indexName: 'age_1', 
			indexCond: { 
				'$and': [ { age: { '$lt': 50 } }, { age: { '$gt': 20 } } ]  
				}, 
			filter: { 
				age: { '$elemMatch': { '$lt': 50, '$gt': 20 } } 
				} 
			}
		}
	}

The new query planner will include index scan support for $regex, without the need of providing $hint in your application code. $regex supports index on prefix searches only:

Sample Document: { "x": [1, 2, 3], "y": "apple" } 
db.foo.createIndex({ "y": 1 })
db.foo.find({"y":{ $regex: "^a" }})

Query Plan – Planner Version 1 .0:

queryPlanner: { 
	plannerVersion: 1, 
	namespace: 'nqptest.foo', 
	winningPlan: { 
		stage: 'COLLSCAN' 
		} 
	}

Query Plan – Planner Version 2.0:

queryPlanner: { 
	plannerVersion: 2, 
	namespace: 'nqptest.foo', 
	winningPlan: { 
		stage: 'FETCH', 
		inputStage: { 
			stage: 'IXSCAN', 
			indexName: 'y_1', 
			indexCond: { 
				'$and': [ { y: { '$regex': /^a/ } } ] } 
				}, 
			filter: { 
				y: { '$regex': /^a/ } 
			} 
		}
	}

Performance optimizations for existing operators

Multiple key performance enhancements have been implemented for existing operators. The NQP improves the performance of queries involving multi-key indexes with equality conditions on a multi-key field:

Sample Document: {"x": [1, 2, 3], "y": 5} 
db.foo.createIndex({"x": 1, "y":1}) 
db.foo.find({"x": 2, "y": {$gt: 1}}).limit(1)

The NQP improves the performance of queries that involve multiple filters, especially on collections with documents greater than 8 KB:

db.foo.find({$and: [{"x": {$gt : 1}, "y": {$gt : 3}, "z": {$lt : 10}, "t":{$lt : 100}}]}

For $in queries with compound indexes, NQP improves performance of queries when using the $in operator with a compound index by eliminating the sort stage:

Sample Document: {"x": 2, "y": 4, "z": 9, "t": 99} 
db.foo.createIndex({"x":1, "y":1}) 
db.foo.find({"x":2, "y":$in:[1,2,3,4]}).sort({x:1,y:1})

Query Plan – Planner Version 1 .0:

queryPlanner: {
    plannerVersion: 1,
    namespace: 'nqptest.foo',
    winningPlan: {
      stage: 'SUBSCAN',
      inputStage: {
        stage: 'SORT',
        sortPattern: { x: 1, y: 1 },
        inputStage: { stage: 'COLLSCAN' }
      }
    }
  }

Query Plan – Planner Version 2 .0:

queryPlanner: {
    plannerVersion: 2,
    namespace: 'nqptest.foo',
    winningPlan: {
      stage: 'IXSCAN',
      indexName: 'x_1_y_1',
      direction: 'forward',
      indexCond: {
        '$and': [
          { x: { '$eq': 2 } },
          { y: { '$in': [ 1, 2, 3, 4 ] } }
        ]
      }
    }
  }

For $in queries with greater than 100 elements, the new query planner improves the performance of queries that use multikey indexes with $in elements:

Sample Document: {"x": [1, 2, 3]} 
db.foo.createIndex({"x": 1}) db.foo.find("x":{$in:[>100 elements]})

Plan cache set filter (server-side hints)

The NQP has added support for index filter features. You can specify a list of indexes that a specific query shape can use. This feature is accessible through our 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.

You can set up a plan cache filter by using the following command:

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

The field "query" and "sort" are used to specify a query shape:

db.runCommand({planCacheSetFilter: "foo", query: { a: 1, b: 1 }, sort: {}, indexes: [ "a_1" ]})

After running the command, the filter on the query shape {a: @, b: @} is ["a_1"]. When an index filter exists for a query shape, the planner will only consider the indexes in the list and come up with the best plan with a subset of them. You can also pass the index definition in place of index name, for example:

db.runCommand({planCacheSetFilter: "foo", query: { a: 1, b: 1 }, sort: {}, indexes: [ {"b": 1}, "b_1_a_1"] }).

To list all filters on the collection, you can use the command below:

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

To verify the plan cache filter usage, there are two new fields: indexFilterSet and indexFilterApplied. In explain plan, indexFilterSet will be true if there’s an index filter set on the collection that matches the query shape. indexFilterApplied will be true if and only if the query applied an index filter and chose a plan using an index in the filter list.

Conclusion

Amazon DocumentDB’s new query planner (NQP) represents a significant leap forward in Amazon DocumentDB performance, introducing improvements that include up to tenfold performance gains for indexed operations. Through enhanced query cost estimation techniques and algorithm optimization, the new query planner delivers the efficient handling of complex operators such as $nin, $ne,$not and nested $elementMatch. With the index filter feature, you can specify a list of indexes that a specific query shape can use. Whether you’re running complex queries or managing large-scale operations, these enhancements can deliver improved performance, stability, and efficiency for your Amazon DocumentDB workloads.

For more information about the new query planner in Amazon DocumentDB, refer to the Query planner documentation.

About the authors