Mongo date range index with filters

Question

We have the below query

db.Comment.find(
    {
        $and: [ 
            { reportCount: { $gt: 0 } },
            { assignee: { $exists: false } }, 
            { creationDate: { $gt: new Date(1507831097809) } },
            { creationDate: { $lt: new Date(1508522297966) } },  
            { siteId: 'MAIN' }, 
            { parent: { $exists: false } }, 
            { status: 'ACTIVE' }
        ]
    })
    .sort({ creationDate: 1 })

And we have an index

 {
    "v" : 2,
    "key" : {
        "creationDate" : 1,
        "reportCount" : 1,
        "label" : 1
    }
}

Here are explain results:

{
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "myNameSpace",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "$and" : [ 
            {
                "siteId" : {
                    "$eq" : "MAIN"
                }
            }, 
            {
                "status" : {
                    "$eq" : "ACTIVE"
                }
            }, 
            {
                "creationDate" : {
                    "$lt" : ISODate("2017-10-20T17:58:17.966Z")
                }
            }, 
            {
                "creationDate" : {
                    "$gt" : ISODate("2017-10-12T17:58:17.809Z")
                }
            }, 
            {
                "reportCount" : {
                    "$gt" : 0.0
                }
            }, 
            {
                "$nor" : [ 
                    {
                        "assignee" : {
                            "$exists" : true
                        }
                    }
                ]
            }, 
            {
                "$nor" : [ 
                    {
                        "parent" : {
                            "$exists" : true
                        }
                    }
                ]
            }
        ]
    },
    "winningPlan" : {
        "stage" : "FETCH",
        "filter" : {
            "$and" : [ 
                {
                    "siteId" : {
                        "$eq" : "MAIN"
                    }
                }, 
                {
                    "status" : {
                        "$eq" : "ACTIVE"
                    }
                }, 
                {
                    "$nor" : [ 
                        {
                            "assignee" : {
                                "$exists" : true
                            }
                        }
                    ]
                }, 
                {
                    "$nor" : [ 
                        {
                            "parent" : {
                                "$exists" : true
                            }
                        }
                    ]
                }
            ]
        },
        "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "creationDate" : 1.0,
                "reportCount" : 1.0,
                "label" : 1.0
            },
            "indexName" : "creationDate_1_reportCount_1_label_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "creationDate" : [],
                "reportCount" : [],
                "label" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "creationDate" : [ 
                    "(new Date(1507831097809), new Date(1508522297966))"
                ],
                "reportCount" : [ 
                    "(0.0, inf.0]"
                ],
                "label" : [ 
                    "[MinKey, MaxKey]"
                ]
            }
        }
    },
    "rejectedPlans" : [ 
        {
            "stage" : "SORT",
            "sortPattern" : {
                "creationDate" : 1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "$nor" : [ 
                                    {
                                        "parent" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }, 
                            {
                                "siteId" : {
                                    "$eq" : "MAIN"
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "ACTIVE"
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$lt" : ISODate("2017-10-20T17:58:17.966Z")
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$gt" : ISODate("2017-10-12T17:58:17.809Z")
                                }
                            }, 
                            {
                                "reportCount" : {
                                    "$gt" : 0.0
                                }
                            }, 
                            {
                                "$nor" : [ 
                                    {
                                        "assignee" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "parent" : 1.0
                        },
                        "indexName" : "parent_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "parent" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "parent" : [ 
                                "[null, null]"
                            ]
                        }
                    }
                }
            }
        }, 
        {
            "stage" : "SORT",
            "sortPattern" : {
                "creationDate" : 1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "$nor" : [ 
                                    {
                                        "assignee" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }, 
                            {
                                "siteId" : {
                                    "$eq" : "MAIN"
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "ACTIVE"
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$lt" : ISODate("2017-10-20T17:58:17.966Z")
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$gt" : ISODate("2017-10-12T17:58:17.809Z")
                                }
                            }, 
                            {
                                "reportCount" : {
                                    "$gt" : 0.0
                                }
                            }, 
                            {
                                "$nor" : [ 
                                    {
                                        "parent" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "assignee" : 1.0
                        },
                        "indexName" : "assignee_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "assignee" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "assignee" : [ 
                                "[null, null]"
                            ]
                        }
                    }
                }
            }
        }, 
        {
            "stage" : "SORT",
            "sortPattern" : {
                "creationDate" : 1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "status" : {
                                    "$eq" : "ACTIVE"
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$lt" : ISODate("2017-10-20T17:58:17.966Z")
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$gt" : ISODate("2017-10-12T17:58:17.809Z")
                                }
                            }, 
                            {
                                "reportCount" : {
                                    "$gt" : 0.0
                                }
                            }, 
                            {
                                "$nor" : [ 
                                    {
                                        "assignee" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }, 
                            {
                                "$nor" : [ 
                                    {
                                        "parent" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "siteId" : 1.0,
                            "updatedDate" : 1.0,
                            "label" : 1.0
                        },
                        "indexName" : "siteId_1_updatedDate_1_label_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "siteId" : [],
                            "updatedDate" : [],
                            "label" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "siteId" : [ 
                                "[\"MAIN\", \"MAIN\"]"
                            ],
                            "updatedDate" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "label" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        }, 
        {
            "stage" : "SORT",
            "sortPattern" : {
                "creationDate" : 1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "$nor" : [ 
                                    {
                                        "parent" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }, 
                            {
                                "$nor" : [ 
                                    {
                                        "assignee" : {
                                            "$exists" : true
                                        }
                                    }
                                ]
                            }, 
                            {
                                "siteId" : {
                                    "$eq" : "MAIN"
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "ACTIVE"
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$lt" : ISODate("2017-10-20T17:58:17.966Z")
                                }
                            }, 
                            {
                                "creationDate" : {
                                    "$gt" : ISODate("2017-10-12T17:58:17.809Z")
                                }
                            }, 
                            {
                                "reportCount" : {
                                    "$gt" : 0.0
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "AND_SORTED",
                        "inputStages" : [ 
                            {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "parent" : 1.0
                                },
                                "indexName" : "parent_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "parent" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "parent" : [ 
                                        "[null, null]"
                                    ]
                                }
                            }, 
                            {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "assignee" : 1.0
                                },
                                "indexName" : "assignee_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "assignee" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "assignee" : [ 
                                        "[null, null]"
                                    ]
                                }
                            }
                        ]
                    }
                }
            }
        }
    ]
},
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 19,
    "executionTimeMillis" : 8,
    "totalKeysExamined" : 533,
    "totalDocsExamined" : 56,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "$and" : [ 
                {
                    "siteId" : {
                        "$eq" : "MAIN"
                    }
                }, 
                {
                    "status" : {
                        "$eq" : "ACTIVE"
                    }
                }, 
                {
                    "$nor" : [ 
                        {
                            "assignee" : {
                                "$exists" : true
                            }
                        }
                    ]
                }, 
                {
                    "$nor" : [ 
                        {
                            "parent" : {
                                "$exists" : true
                            }
                        }
                    ]
                }
            ]
        },
        "nReturned" : 19,
        "executionTimeMillisEstimate" : 0,
        "works" : 534,
        "advanced" : 19,
        "needTime" : 513,
        "needYield" : 0,
        "saveState" : 20,
        "restoreState" : 20,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 56,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 56,
            "executionTimeMillisEstimate" : 0,
            "works" : 533,
            "advanced" : 56,
            "needTime" : 476,
            "needYield" : 0,
            "saveState" : 20,
            "restoreState" : 20,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "creationDate" : 1.0,
                "reportCount" : 1.0,
                "label" : 1.0
            },
            "indexName" : "creationDate_1_reportCount_1_label_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "creationDate" : [],
                "reportCount" : [],
                "label" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "creationDate" : [ 
                    "(new Date(1507831097809), new Date(1508522297966))"
                ],
                "reportCount" : [ 
                    "(0.0, inf.0]"
                ],
                "label" : [ 
                    "[MinKey, MaxKey]"
                ]
            },
            "keysExamined" : 533,
            "seeks" : 477,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }
    }
},

"ok" : 1.0
}

The query is still taking 700-800 ms to return the data. How can I change the index to make the query run faster? Don't consider "keysExamined" : 533, "seeks" : 477, This data. This is just test data.

Looks like its using an index but only the first field in the index? Also multuKey is false?

Answer 1

A few key points from the explain plan output:

• The query addresses the following attributes: siteId, status, creationDate, reportCount, assignee, parent
• The winning plan has two stages:
  • IX_SCAN uses creationDate_1_reportCount_1_label_1, this uses indexed lookups on creationDate and reportCount to identify 56 documents which are then forwarded to the FETCH stage
  • FETCH receives 56 documents from the IX_SCAN stage and then interrogates these documents to apply the siteId, status, assignee and parent filters. This interrogation causes 37 documents to be discarded resulting in 19 document to be returned.

So, your index covers just 2 of the 6 attributes in your query and the remaining 4 attributes in your query are applied by examining the documents not the index. If you want this query to be fully index covered then create the following index:

db.collection.createIndex(
    {siteId: 1, status: 1, creationDate: 1, reportCount: 1, assignee: 1, parent: 1}
) 

If you re run with this index in place then you should find that (a) MongoDB chooses this index and (b) the number of documents forwarded by the IX_SCAN stage is the same as the number of documents returned by your find call.

I say "should find" because there are other aspects here which might result in MongoDB choosing a different index e.g. use of $nor and the sort stage (creationDate: 1). I would recommend tweaking the index and running with explain 'on' after each tweak and looking for these key items in the executionStats sub document:

• "nReturned"
• "totalKeysExamined"
• "totalDocsExamined"

A simple rule of thumb is this: the closer totalKeysExamined is to nReturned and the closer totalDocsExamined is to zero ... the better your index coverage.

There is also the question of the cost of an index (in terms of impact on write times and index storage) so I'd suggest considering your non functional requirements - can your desired elapsed times be achieved without full index coverage? If not, then you should proceed with empirical testing but be prepared to tweak your choice in reponse to what the explain() output tells you.