[SERVER-73423] CLUSTERED_IXSCAN with sort generates two duplicate plans Created: 27/Jan/23  Updated: 29/Oct/23  Resolved: 16/Feb/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.0.0-rc0, 6.3.0-rc1, 6.0.7

Type: Bug Priority: Major - P3
Reporter: Alyssa Clark Assignee: Ivan Fefer
Resolution: Fixed Votes: 0
Labels: quick-tech-debt
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
depends on SERVER-73009 Decreasing order sort on clustered co... Closed
Problem/Incident
is caused by SERVER-60824 Support non-blocking sort() on cluste... Closed
is caused by SERVER-58276 Add time-series bounded collection sc... Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v6.3, v6.0
Sprint: QE 2023-02-20
Participants:

 Description   

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
        }
    ]
} 



 Comments   
Comment by Githook User [ 24/May/23 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-73423 Remove duplicate addition of collection scan in case of clustered collection

(cherry picked from commit 89b0377ea5c62e5a72469d6072aaa146a700b2b6)
Branch: v6.0
https://github.com/mongodb/mongo/commit/8a0c330c0c6c1a7d0b0ec85c7b9d80e85812cade

Comment by Githook User [ 20/Feb/23 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-73423 Remove duplicate addition of collection scan in case of clustered collection
Branch: v6.3
https://github.com/mongodb/mongo/commit/89b0377ea5c62e5a72469d6072aaa146a700b2b6

Comment by Githook User [ 16/Feb/23 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-73423 Remove duplicate addition of collection scan in case of clustered collection
Branch: master
https://github.com/mongodb/mongo/commit/5cbdfbd6cdccdd8f53f982c417e13b7fec2ec566

Comment by Ivan Fefer [ 15/Feb/23 ]

Looks like it is caused by my changes. Should be easy to fix.

Comment by Ana Meza [ 14/Feb/23 ]

Passing to the QE backlog - ivan.fefer@mongodb.com would you mind looking at this please

Comment by Alyssa Clark [ 30/Jan/23 ]

This issue didn't exist until SERVER-58276, which is marked as 6.2.0-rc0, so we shouldn't need to backport to 6.0 - possibly just 6.2 or 6.3 depending on when this is completed.

Comment by Kyle Suarez [ 27/Jan/23 ]

alyssa.wagenmaker@mongodb.com, do we know if we need to request backport for this? (Presumably to 6.0?)

Generated at Thu Feb 08 06:24:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.