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

      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: