Index scan not filtering as expected

Working with Data
1

I have a query that uses an index but during the fetch is looking up too many documents.

The index in question is:

{
“v” : 2,
“key” : {
“vw” : -1,
“if” : 1,
“sa” : 1,
“dd” : -1,
“ca” : 1
},
“name” : “Viewed_By_Category”,
“ns” : “redacted”,
“background” : false
}

the query in question:

db.stories.find({ 'if': {$ne: true}, 'sa': 2, 'dd': {$ne : null}, 'ca': 11}).skip(3990).limit(30).sort({'vw':-1}).explain('executionStats')

and this is the explain output:

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "lushstories.stories",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "ca" : {
                                                "$eq" : 11
                                        }
                                },
                                {
                                        "sa" : {
                                                "$eq" : 2
                                        }
                                },
                                {
                                        "dd" : {
                                                "$not" : {
                                                        "$eq" : null
                                                }
                                        }
                                },
                                {
                                        "if" : {
                                                "$not" : {
                                                        "$eq" : true
                                                }
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "LIMIT",
                        "limitAmount" : 30,
                        "inputStage" : {
                                "stage" : "SKIP",
                                "skipAmount" : 0,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "ca" : {
                                                                        "$eq" : 11
                                                                }
                                                        },
                                                        {
                                                                "sa" : {
                                                                        "$eq" : 2
                                                                }
                                                        },
                                                        {
                                                                "dd" : {
                                                                        "$not" : {
                                                                                "$eq" : null
                                                                        }
                                                                }
                                                        },
                                                        {
                                                                "if" : {
                                                                        "$not" : {
                                                                                "$eq" : true
                                                                        }
                                                                }
                                                        }
                                                ]
                                        },
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "vw" : -1,
                                                        "if" : 1,
                                                        "sa" : 1,
                                                        "dd" : -1,
                                                        "ca" : 1
                                                },
                                                "indexName" : "Viewed_By_Category",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "vw" : [ ],
                                                        "if" : [ ],
                                                        "sa" : [ ],
                                                        "dd" : [ ],
                                                        "ca" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "vw" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "if" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "sa" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "dd" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "ca" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "SKIP",
                                "skipAmount" : 3990,
                                "inputStage" : {
                                        "stage" : "SORT",
                                        "sortPattern" : {
                                                "vw" : -1
                                        },
                                        "limitAmount" : 4020,
                                        "inputStage" : {
                                                "stage" : "SORT_KEY_GENERATOR",
                                                "inputStage" : {
                                                        "stage" : "FETCH",
                                                        "filter" : {
                                                                "dd" : {
                                                                        "$not" : {
                                                                                "$eq" : null
                                                                        }
                                                                }
                                                        },
                                                        "inputStage" : {
                                                                "stage" : "IXSCAN",
                                                                "keyPattern" : {
                                                                        "dd" : -1,
                                                                        "if" : 1,
                                                                        "sa" : 1,
                                                                        "ca" : 1,
                                                                        "ha" : 1
                                                                },
                                                                "indexName" : "Story_Visible_With_Audio",
                                                                "isMultiKey" : false,
                                                                "multiKeyPaths" : {
                                                                        "dd" : [ ],
                                                                        "if" : [ ],
                                                                        "sa" : [ ],
                                                                        "ca" : [ ],
                                                                        "ha" : [ ]
                                                                },
                                                                "isUnique" : false,
                                                                "isSparse" : false,
                                                                "isPartial" : false,
                                                                "indexVersion" : 2,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "dd" : [
                                                                                "[MaxKey, null)",
                                                                                "(null, MinKey]"
                                                                        ],
                                                                        "if" : [
                                                                                "[MinKey, true)",
                                                                                "(true, MaxKey]"
                                                                        ],
                                                                        "sa" : [
                                                                                "[2.0, 2.0]"
                                                                        ],
                                                                        "ca" : [
                                                                                "[11.0, 11.0]"
                                                                        ],
                                                                        "ha" : [
                                                                                "[MinKey, MaxKey]"
                                                                        ]
                                                                }
                                                        }
                                                }
                                        }
                                }
                        }
                ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 30,
                "executionTimeMillis" : 5500,
                "totalKeysExamined" : 55743,
                "totalDocsExamined" : 55743,
                "executionStages" : {
                        "stage" : "LIMIT",
                        "nReturned" : 30,
                        "executionTimeMillisEstimate" : 5372,
                        "works" : 55744,
                        "advanced" : 30,
                        "needTime" : 55713,
                        "needYield" : 0,
                        "saveState" : 565,
                        "restoreState" : 565,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "limitAmount" : 30,
                        "inputStage" : {
                                "stage" : "SKIP",
                                "nReturned" : 30,
                                "executionTimeMillisEstimate" : 5372,
                                "works" : 55743,
                                "advanced" : 30,
                                "needTime" : 55713,
                                "needYield" : 0,
                                "saveState" : 565,
                                "restoreState" : 565,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "skipAmount" : 0,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "$and" : [
                                                        {
                                                                "ca" : {
                                                                        "$eq" : 11
                                                                }
                                                        },
                                                        {
                                                                "sa" : {
                                                                        "$eq" : 2
                                                                }
                                                        },
                                                        {
                                                                "dd" : {
                                                                        "$not" : {
                                                                                "$eq" : null
                                                                        }
                                                                }
                                                        },
                                                        {
                                                                "if" : {
                                                                        "$not" : {
                                                                                "$eq" : true
                                                                        }
                                                                }
                                                        }
                                                ]
                                        },
                                        "nReturned" : 4020,
                                        "executionTimeMillisEstimate" : 5372,
                                        "works" : 55743,
                                        "advanced" : 4020,
                                        "needTime" : 51723,
                                        "needYield" : 0,
                                        "saveState" : 565,
                                        "restoreState" : 565,
                                        "isEOF" : 0,
                                        "invalidates" : 0,
                                        "docsExamined" : 55743,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 55743,
                                                "executionTimeMillisEstimate" : 80,
                                                "works" : 55743,
                                                "advanced" : 55743,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 565,
                                                "restoreState" : 565,
                                                "isEOF" : 0,
                                                "invalidates" : 0,
                                                "keyPattern" : {
                                                        "vw" : -1,
                                                        "if" : 1,
                                                        "sa" : 1,
                                                        "dd" : -1,
                                                        "ca" : 1
                                                },
                                                "indexName" : "Viewed_By_Category",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "vw" : [ ],
                                                        "if" : [ ],
                                                        "sa" : [ ],
                                                        "dd" : [ ],
                                                        "ca" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "vw" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "if" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "sa" : [
                                                                "[MinKey, MaxKey]"
                                                        ],
                                                        "dd" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "ca" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                },
                                                "keysExamined" : 55743,
                                                "seeks" : 1,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0
                                        }
                                }
                        }
                }
        },
        "serverInfo" : {
                "host" : "redacted",
                "port" : 27017,
                "version" : "4.0.9",
                "gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
        },
        "ok" : 1
}

So why is the IXSCAN scan stage not using any of the predicates to filter, the indexBounds are all using [MaxKey, MinKey]

This is returning the full number of records 55743 which is being fed into the fetch.

Is there something I dont understand about these indexes?

Thanks

2

Hello Gavin,

Here are some details about using indexes with query filter and the sort operations: Use Indexes to Sort Query Results. Specifically see the sub-topics Sort and Index Prefix and Sort and Non-prefix Subset of an Index.

The sort operation of your query is using the index for sure (noted by the missing SORT stage in the plan’s “winningPlan”).

3

Thanks for the reply.

So I can see the index is being used, its just that to me there are no filters being applied.

The sort column (vw) is the first in the index. The remaining fields all appear in the covering index (if, sa, dd, ca) and i would expect these values in the predicate to be used, not:

“sa” : [ “[MinKey, MaxKey]” ]

but more like something like

“sa” : [ “[1, 1]” ]

Using the same filter returns a count of 4647, so i would expect at least this number passed to the fetch, not 55743

Thanks

4

You may want to try using a compound index with the keys sa + ca + vw (in that order). This will likely result in a query filter using the index on the fields sa and ca. Then the sort on vw too uses the index. Whether the index prefix should be sa + ca or ca + sa, you have to figure based upon the number of documents returned on the first key (see Create Queries that Ensure Selectivity).

5

Thanks for that.
Ended up with the following index that worked

{“ca” : 1, “sa” : 1, “vw”:-1, “dd” : -1, “if” : 1}

6

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.