Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-73423

CLUSTERED_IXSCAN with sort generates two duplicate plans

    • Query Execution
    • Fully Compatible
    • ALL
    • v6.3, v6.0
    • QE 2023-02-20

      When we have a query on a clustered collection that both filters and sorts on the clustered index key, we generate two of the same plan.

      First time:

      https://github.com/10gen/mongo/blob/726e809bf3789c91c108b1db1e7edcc88a0f4c3e/src/mongo/db/query/query_planner.cpp#L1533

      Then again here:

      https://github.com/10gen/mongo/blob/726e809bf3789c91c108b1db1e7edcc88a0f4c3e/src/mongo/db/query/query_planner.cpp#L1622 

      This doesn’t cause incorrect results because both plans are correct, but it will make us cache plans and do multi planning in situations where we otherwise wouldn’t have to.

      MongoDB Enterprise > db.createCollection("clustered_collection", {clusteredIndex: {key: {_id: 1}, unique: true}})
      { "ok" : 1 }
      MongoDB Enterprise > db.clustered_collection.find({_id: 5}).sort({_id: 1}).explain().queryPlanner
      {
          "namespace" : "test2.clustered_collection",
          "indexFilterSet" : false,
          "parsedQuery" : {
              "_id" : {
                  "$eq" : 5
              }
          },
          "queryHash" : "5B20DB79",
          "planCacheKey" : "5B20DB79",
          "maxIndexedOrSolutionsReached" : false,
          "maxIndexedAndSolutionsReached" : false,
          "maxScansToExplodeReached" : false,
          "winningPlan" : {
              "stage" : "CLUSTERED_IXSCAN",
              "filter" : {
                  "_id" : {
                      "$eq" : 5
                  }
              },
              "direction" : "forward",
              "minRecord" : 5,
              "maxRecord" : 5
          },
          "rejectedPlans" : [
              {
                  "stage" : "CLUSTERED_IXSCAN",
                  "filter" : {
                      "_id" : {
                          "$eq" : 5
                      }
                  },
                  "direction" : "forward",
                  "minRecord" : 5,
                  "maxRecord" : 5
              }
          ]
      } 

            Assignee:
            ivan.fefer@mongodb.com Ivan Fefer
            Reporter:
            alyssa.clark@mongodb.com Alyssa Clark
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: