0

I have a mongodb (V3.6.10) collection with the following stats&indexes:

{
    "ns" : "RepoProd.raiding",
    "size" : 145312567936,
    "count" : 6776728,
    "avgObjSize" : 21442,
    "numExtents" : 88,
    "storageSize" : 146677222384,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "paddingFactorNote" : "paddingFactor is unused and unmaintained in 3.0. It remains hard coded to 1.0 for compatibility only.",
    "userFlags" : 1,
    "capped" : false,
    "nindexes" : 7,
    "totalIndexSize" : 2848379408,
    "indexSizes" : {
            "_id_" : 244315232,
            "project_1_data_id_1" : 394835392,
            "date" : 283183936,
            "project_1_date_-1" : 309159088,
            "project_1_type_1_raiding_format_1_date_-1" : 646051168,
            "project_1_type_1_raiding_format_1" : 571379760,
            "project_1_average_duration_1" : 399454832
    },
    "ok" : 1,
    "operationTime" : Timestamp(1561912379, 32),
    "$clusterTime" : {
            "clusterTime" : Timestamp(1561912379, 32),
            "signature" : {
                    "hash" : BinData(0,"nHYZ4Wo1NlFq8vkMRIBkDgUHI1Q="),
                    "keyId" : NumberLong("6680409986377449473")
            }
    }
}

I'm trying to optimize a query and i found that while filtering on a specific range of dates, the optimizer refuses to consider both "edges" of the filter and uses only one, resulting in many documents scanned for no reason. the complete query looks like this:

db.raiding.find({ project: "XXX",
raiding_format: { $in: [ 1, 2, 3, 4 ] },
date: { $lte: new Date(1561852800000), $gte: new Date(1556668800000) },
template_fk: { $in: [ 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39 ] },
type: "XXXX", data_id: { $nin: [ 36199426, 36161176, 36187613, 36101363, 36108504, 36258306, 36266484, 36189501, 36226655, 36097488, 36261129, 36275154, 36175940, 36231966, 36251535, 36228915, 36095058, 36285670, 36280196, 36260714, 36222538, 36293417, 36251715, 36139034, 36185710, 36240811, 36161462, 36141334, 36136585, 36133349, 36146764, 36159163, 36261128, 36188455, 36118209, 36131352, 36168139, 36239066, 36210447, 36293746, 36244296, 36235466, 36290147, 36257640, 36163667, 36291836, 36228062, 36231507, 36233017, 36147164, 36286387, 36099974, 36149823, 36169567, 36274461, 36145107, 36172680, 36212643, 36194795, 36278193, 36200852, 36228329, 36145803, 36247697, 36128362, 36206059, 36145751, 36126484, 36113834, 36268827, 36249010, 36138966, 36255412, 36229323, 36211528, 36151986, 36098544, 36099498, 36212007, 36218891, 36227708, 36167197, 36211256, 36239717, 36258801, 36288101, 36099165, 36214641, 36256846, 36209899, 36288655, 36100353, 36202220, 36227892, 36129979, 36134772, 36201283, 36110860, 36225400, 36271883, 36201075, 36103144, 36195552, 36207161, 36184214, 36111239, 36107272, 36091687]}})

even when i use a simpler query, the winning plan looks like this:

db.raiding.find({ project: "XXX",
date: { $lte: new Date(1561852800000), $gte: new Date(1556668800000) }).explain().queryPlanner.winningPlan

{
    "stage" : "FETCH",
    "filter" : {
            "date" : {
                    "$gte" : ISODate("2019-05-01T00:00:00Z")
            }
    },
    "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                    "project" : 1,
                    "date" : -1
            },
            "indexName" : "project_1_date_-1",
            "isMultiKey" : true,
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                    "project" : [
                            "[\"XXX\", \"XXX\"]"
                    ],
                    "date" : [
                            "[new Date(1561852800000), true)"
                    ]
            }
    }
}

Why does it refuse to consider both dates in the index scan?

  • Hi, will yo be able to post the executionStats and a sample document? – Mani Jul 3 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.