[SERVER-34355] Mongo DB aggregation Query Performance Created: 06/Apr/18  Updated: 06/Dec/22  Resolved: 09/Apr/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Index Maintenance
Affects Version/s: 3.6.1
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: srivani kambhampati Assignee: Backlog - Triage Team
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Server Triage
Participants:

 Description   

Dear Team,

I have document structure some thing like below

// Some comments here
{ 
    "_id" : ObjectId("5ac05dc58ff6cd3054d5654c"), 
    "origin" : {
        "satellite" : [
            "HuJpO", 
            "rBQXN"
        ], 
        "code" : "0000", 
        "lat" : 0.10894239962556962, 
        "long" : 0.3182917328356123, 
        "state" : "7MJA8", 
        "mother" : "RNzTT"
    }, 
    "destination" : {
        "satellite" : [
            "SFrLs", 
            "PB22x"
        ], 
        "code" : "0001", 
        "lat" : 0.9059857126884281, 
        "long" : 0.47366986861781046, 
        "state" : "iGPdL", 
        "mother" : "rmL2i"
    }, 
    "currentOutput" : {
        "originprops" : {
            "terminalType" : "7Vsdd", 
            "region" : "Wq13p"
        }, 
        "destprops" : {
            "terminalType" : "Lj9MU", 
            "region" : "FsveA"
        }, 
        "metricsOnRoute" : {
            "wtOnRoute" : 0.12848451356908464, 
            "cubeOnRoute" : 0.36253653173815015, 
            "packagesOnRoute" : 0.7116535335079031
        }, 
        "ftlMetricsOnRoute" : {
            "wtOnRoute" : 0.2867262438925031, 
            "cubeOnRoute" : 0.041862427079534026, 
            "packagesOnRoute" : 0.1922009517509271
        }, 
        "loadPlan" : [
            {
                "origin" : {
                    "satellite" : [
                        "Eeu64", 
                        "UoBZg"
                    ], 
                    "code" : "0895", 
                    "lat" : 0.886633114334241, 
                    "long" : 0.7648130191577616, 
                    "state" : "e6YVK", 
                    "mother" : "seZRD", 
                    "region" : "YH3l0", 
                    "terminalType" : "6eRPl"
                }, 
                "destination" : {
                    "satellite" : [
                        "HsXLj", 
                        "3owI3"
                    ], 
                    "code" : "0582", 
                    "lat" : 0.2404112136392642, 
                    "long" : 0.4453750293428518, 
                    "state" : "24y3J", 
                    "mother" : "Al228", 
                    "region" : "BnOho", 
                    "terminalType" : "J5bzc"
                }, 
                "_id" : ObjectId("5ac05dc58ff6cd3054d56550")
            }, 
            {
                "origin" : {
                    "satellite" : [
                        "dsP96", 
                        "kxB4x"
                    ], 
                    "code" : "0889", 
                    "lat" : 0.7451116916804306, 
                    "long" : 0.5827170158371271, 
                    "state" : "rlab1", 
                    "mother" : "kAdCk", 
                    "region" : "K4Fmv", 
                    "terminalType" : "1Rmq9"
                }, 
                "destination" : {
                    "satellite" : [
                        "f9Dsw", 
                        "nWtR1"
                    ], 
                    "code" : "0143", 
                    "lat" : 0.6918117740443803, 
                    "long" : 0.6534881444120195, 
                    "state" : "M8rJ9", 
                    "mother" : "SAIay", 
                    "region" : "gj2GK", 
                    "terminalType" : "WxFMb"
                }, 
                "_id" : ObjectId("5ac05dc58ff6cd3054d5654f")
            }, 
            {
                "origin" : {
                    "satellite" : [
                        "OEMn4", 
                        "L06I7"
                    ], 
                    "code" : "0019", 
                    "lat" : 0.2928144478088912, 
                    "long" : 0.48588766114875814, 
                    "state" : "CiZfK", 
                    "mother" : "JhK2x", 
                    "region" : "30LxB", 
                    "terminalType" : "2T5h2"
                }, 
                "destination" : {
                    "satellite" : [
                        "MYlrI", 
                        "chBxa"
                    ], 
                    "code" : "0127", 
                    "lat" : 0.1475212251052762, 
                    "long" : 0.5700208781264593, 
                    "state" : "eq4Tz", 
                    "mother" : "VWGka", 
                    "region" : "4haP1", 
                    "terminalType" : "hPoJN"
                }, 
                "_id" : ObjectId("5ac05dc58ff6cd3054d5654e")
            }, 
            {
                "origin" : {
                    "satellite" : [
                        "Dn5gk", 
                        "E8ioC"
                    ], 
                    "code" : "0261", 
                    "lat" : 0.5357177690737176, 
                    "long" : 0.24077439863344718, 
                    "state" : "ifZma", 
                    "mother" : "Qw3JQ", 
                    "region" : "535wA", 
                    "terminalType" : "OWDMq"
                }, 
                "destination" : {
                    "satellite" : [
                        "KMC4g", 
                        "TRM2g"
                    ], 
                    "code" : "0364", 
                    "lat" : 0.0853362441968799, 
                    "long" : 0.17485240248316103, 
                    "state" : "OT3OB", 
                    "mother" : "4ziG5", 
                    "region" : "r5QXn", 
                    "terminalType" : "BZr3o"
                }, 
                "_id" : ObjectId("5ac05dc58ff6cd3054d5654d")
            }
        ], 
        "avgMiles" : 0.15093020854848138, 
        "avgDelay" : 0.8286008015547, 
        "avgEarlyArrival" : 0.49055776886519387, 
        "delayPenalityCost" : 0.36795929885604606, 
        "percentWrDelay" : 0.17503869241793435, 
        "percentWtOnRail" : 0.06687894944310369, 
        "numberOfRehandlings" : 0.7399530304098465, 
        "numberOfRelay" : 0.6613467970393607, 
        "loadPlanString" : "0895-0889-0019-0261-0364", 
        "dispatchPlanString" : "0895-0889-0019-0261-0364", 
        "loadPlanStringWithType" : "0895-0889-0019-0261-0364"
    }, 
    "scenarioId" : NumberInt(0), 
    "serviceType" : "ECON", 
    "__v" : NumberInt(0)
}

This collection could potentially hold millions of data per scenario.
In my aggregation pipeline post i apply scenario filter potentially i can pass millions of data to next stage of the pipeline.

currently we have a limit of 100MB and i need to allowdiskuse, and i get the result back in 100+ secs.

My query looks like below.

// Some comments here
db.servicestats.aggregate([{
    $match: {
        scenarioId: { $in: [ 0, 1 ] }
    }
}, 
{
    $sort: { 
        'origin.code': 1,
        'destination.code': 1,
        serviceType: 1
    }
},
 {
    $group: { 
        _id: {
            originCode: '$origin.code',
            destinationCode: '$destination.code',
            serviceType: '$serviceType'
        },
        baseScenarioAvgMiles: {
            $max: {
                $cond: {
                    if: { $eq: [ '$scenarioId', 1 ] },
                    then: '$currentOutput.avgMiles',
                    else: 0
                }
            }
        },
        compareScenarioAvgMiles: {
            $max: {
                $cond: {
                    if: { $eq: [ '$scenarioId', 0 ] },
                    then: '$currentOutput.avgMiles',
                    else: 0
                }
            }
        }
    },
},
 {
    $addFields: { // compute the difference
        diff: {
            $subtract :[ '$baseScenarioAvgMiles', '$compareScenarioAvgMiles']
        }
    }
}, 
{
    $match: {
        'diff': { $gt: 0.5 }
    }
}, 
{
    $limit:100
}],{allowDiskUse:false})

Any suggestions so that i can improve my query performance? Currently i have indexes on my group condition and also on scenarioId.

In the document i would need all fields as i can potentially show all columns on UI and filter on any them.



 Comments   
Comment by Ramon Fernandez Marina [ 09/Apr/18 ]

Thanks for your report. Please note that the SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag, where your question will reach a larger audience. A question like this involving more discussion would be best posted on the mongodb-user group. See also our Technical Support page for additional support resources.

Regards,
Ramón.

Generated at Thu Feb 08 04:36:23 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.