[SERVER-73486] Prevent plans with unhelpful ("no-op") full index scans Created: 31/Jan/23  Updated: 01/Feb/23

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Chris Harris Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Optimization
Participants:

 Description   

Consider the following:
 

> db.foo.find({z:1, $or:[{x:{$in:[1]}},{x:{ $nin:[1]}}]}).explain().queryPlanner.winningPlan
{
    "stage" : "FETCH",
    "filter" : {
        "z" : {
            "$eq" : 1
        }
    },
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "x" : 1
        },
        "indexName" : "x_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "x" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "x" : [
                "[MinKey, MaxKey]"
            ]
        }
    }
}

Here the database is performing a full index scan which is providing no value. Apart from the scan being unbounded, there is no filter being applied in the IXSCAN stage nor is the index supporting a sort order requested from a client. Such a plan does nothing but slow down the query and therefore should not be considered.


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