CLUSTERED_IXSCAN with sort generates two duplicate plans

XMLWordPrintableJSON

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

      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
              Reporter:
              Alyssa Clark
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

                Created:
                Updated:
                Resolved: