[SERVER-53928] Don't consider wildcard plans when a perfect specific index exists Created: 20/Jan/21  Updated: 06/Dec/22

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

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

Assigned Teams:
Query Optimization
Operating System: ALL
Participants:

 Description   

If a specific index exists it is probably always better to use than the wildcard index. If not, we should probably warn or maybe even disallow adding the otherwise redundant index.

One way to do this would be to model the wildcard index purely as a fallback in the QueryPlanner so that when it would go to use an index on a field but there is no index it can use, it will instead make a wildcard scan as-if there was that index on that field. I think that would also solve SERVER-53927

> db.foo.ensureIndex({'$**': 1})
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.foo.ensureIndex({a: 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 3,
        "ok" : 1
}
> db.foo.find({a:1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "a" : {
                                "$eq" : 1
                        }
                },
                "queryHash" : "4B53BE76",
                "planCacheKey" : "4281AFAD",
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "$_path" : 1,
                                        "a" : 1
                                },
                                "indexName" : "$**_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "$_path" : [ ],
                                        "a" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "$_path" : [
                                                "[\"a\", \"a\"]"
                                        ],
                                        "a" : [
                                                "[1.0, 1.0]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "FETCH",
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "a" : 1
                                        },
                                        "indexName" : "a_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "a" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "a" : [
                                                        "[1.0, 1.0]"
                                                ]
                                        }
                                }
                        }
                ]
        },
        "serverInfo" : {
                "host" : "silversurfer-wsl",
                "port" : 27017,
                "version" : "4.4.3",
                "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
        },
        "ok" : 1
}


Generated at Thu Feb 08 05:32:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.