Help Center/ Document Database Service/ Best Practices/ DDS Query Plans and Query Replanning
Updated on 2025-03-26 GMT+08:00

DDS Query Plans and Query Replanning

A DDS query plan is a detailed process that determines how a query is executed. The DDS query planner chooses an execution plan based on the query criteria, data distribution, and index information. However, in some cases, DDS re-evaluates and generates a new query plan, that is, query replanning. Learning query replanning helps improve database performance and effectively reduce resource waste.

The following describes how DDS query plans work, query replanning scenarios, and best practices for handling and optimizing query replanning.

Query Plan Overview

A query plan is an execution path selected by the DDS query planner when a query is executed. This execution path defines how to scan data, whether to use indexes, and how to process each query phase. DDS generates a query plan in the following steps:

  1. Parsing a query: DDS parses query criteria and identifies fields and operators.
  2. Selecting indexes: Based on the query criteria, DDS checks available indexes and chooses the most appropriate index to accelerate the query.
  3. Evaluating execution paths: DDS evaluates possible execution paths based on the data volume, field selectivity, and index type, and chooses the optimal execution path to generate a new query plan.
  4. Executing the query: DDS executes the query based on the selected query plan and returns the result.

Query Replanning Overview

Query replanning refers to the process in which DDS re-evaluates and generates a new query plan based on data or query structure changes during query execution. When query replanning occurs, DDS discards the previously selected query plan and uses the new execution path.

The main purpose of query replanning is to ensure that the query can adapt to new data distribution, index changes, and load changes when the data environment changes, thereby maintaining stable query performance.

Query Replanning Scenarios

The following lists some common query replanning scenarios:

  • Index changes

    Adding or deleting indexes: When indexes of a field change (for example, an index is added or deleted), DDS may replan the query execution plan. The query may re-evaluate the validity of existing indexes and then chooses a new index, or decide to use a full table scan.

  • Data distribution changes

    If data distribution changes (for example, the selectivity of a field changes significantly), DDS may re-evaluate the query plan to ensure that the optimal execution path can be chosen. For example, a field becomes sparser or denser, resulting in a change in index efficiency.

  • Query criteria changes

    When query criteria change, DDS may need to re-generate a query plan. For example, new filter criteria are added to some queries, or different operators are used in the query criteria (for example, from $eq to $in).

  • Aggregation pipeline changes

    During an aggregation operation, if the data volume, field, or pipeline sequence changes, DDS may re-evaluate the execution plan of the aggregation pipeline. Especially when the data filter criteria in the pipeline phase change, DDS may reselect an execution path.

  • Environment resource load changes

    If an instance load changes (for example, node resources are used up, the storage space is insufficient, or the network latency increases), DDS may adjust the query plan to optimize the query performance under the current resource conditions.

  • Invalid query cache

    DDS caches the optimal query plan that is executed previously. In some cases, DDS may use the query cache. If the query cache becomes invalid, a new query plan may be regenerated.

Impacts of Query Replanning

When query replanning occurs, the keyword replanned:1 is displayed in the slow query log, indicating that the database cannot provide an always valid plan for the query conditions of the current query.

Frequent query replanning has the following impacts:

  • Increased CPU overhead: Frequent replanning consumes more CPU resources and affects the overall performance.
  • Increased memory usage: Frequent replanning increases the memory usage and may affect other operations.
  • Increased query delay: Each replanning increases the query execution time. As a result, the response becomes slow.

Handling Suggestions for Query Replanning

  • Periodically monitor query plans.

    You are advised to periodically monitor query execution plans, especially after a system changes, for example, adding indexes, adding data, or modifying the query structure. Using either of the following methods:

    • Use the explain() method to analyze the execution plan of a query.
    • Periodically check slow query logs to learn about possible query plan changes.
  • Check the cause of a query plan change.

    When query replanning occurs, you need to determine which factors cause the change of a query plan. Check the following causes:

    • Index changes: Run db.collection.getIndexes() to check whether indexes are added or deleted.
    • Data distribution: Check whether data changes significantly based on statistics. For example, you can run db.collection.stats() to view collection statistics, such as the number of documents, data size, and index size.
    • Query criteria: Check whether the query criteria are changed, whether a new field is added, or whether the query operators are changed.
  • Optimize query replanning.

    The following are some suggestions for optimizing query replanning:

    • [Recommended] Ensure that the database environment has sufficient resources (such as CPUs, memory, and storage) to handle the load, reduce query replanning caused by resource limitations, and upgrade the instance specifications to relieve the database load. For details, see Changing an Instance Class.
    • [Recommended] Predict data distribution. During index design, you can use data distribution prediction to choose proper fields for indexing to prevent frequent query replanning caused by data distribution changes.
    • [Recommended] Use stable indexes. Ensure that stable and proper indexes are provided for common queries. Especially when there is a large volume of data, indexes can effectively reduce the occurrence of query replanning.
    • [Recommended] Avoid frequently changing query structures. Frequent changes of query criteria may cause DDS to frequently re-generate query plans. You are advised to ensure the consistency of query criteria based on service requirements.
    • [Recommended] Optimize aggregation pipelines. If your query involves a complex aggregation pipeline, you are advised to periodically analyze the execution plan of the pipeline and optimize it. You can use $match to filter data in the early stage of the aggregation pipeline to reduce the amount of data processed in subsequent stages.
    • Use hint() to specify an index for query criteria that are replanned. Calling this method for a query can overwrite the default index selection and query optimization process of DDS. Example:
      db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1})
    • Use index filtering to restrict indexes that you want to use for query criteria that are replanned. Index filters override the expected behavior of the query planner in selecting query plans. If you specify a hint and an index filter for a query, the index filter overwrites the specified hint. Therefore, use the index filter with caution. The following example creates an index filter for the orders collection. This filter is suitable for queries whose predicate is an equivalent match of the item field, where only the quantity field is projected, and sorting is specified by order_date in ascending order.
      db.runCommand(
         {
            planCacheSetFilter: "orders",
            query: { item: "ABC" },
            projection: { quantity: 1, _id: 0 },
            sort: { order_date: 1 },
            indexes: [
               { item: 1, order_date: 1 , quantity: 1 }
            ]
         }
      )

      For plan cache query structures, the query planner will only consider index plans that use index { item: 1, order_date: 1, quantity: 1 }.

Evaluating Query Replanning and Taking Measures

Query replanning may change an execution plan, which affects the query performance. When query replanning occurs, you need to evaluate the change of the query performance, check whether the query performance is improved or decreased, and take measures accordingly.

  • Performance deterioration: If the query performance deteriorates due to query replanning, you can add or adjust indexes to improve the performance.
  • Performance improvement: If a query execution after replanning is more efficient, you can keep the current execution plan and monitor whether there are other potential performance bottlenecks.