Aggregation query not covered

Hi,

I am trying to wrap my head around covered queries in context of aggregations, but firstly, mongodb cluster specs:

  • version 4.2.12
  • 3x amazon t3.large (2CPU, 8GB RAM)
  • 1 master, 2 secondaries, no sharding

I tried to cover my query with compound index, but the explain shows “PROJECTION_DEFAULT” instead of “PROJECTION_COVERED”, it takes ~12 seconds to execute on 1 million docs, and have to use disk.

Index:

{
    "Meta.FlowId" : 1,
    "Meta.UpstreamMessageId" : 1,
    "MessageType" : 1,
    "Meta.TrackingId" : 1,
    "Status" : 1,
    "HandleResult.HandleStatus" : 1
}

Aggregation query:

db.getCollection('FlowMessageInfo').explain("executionStats").aggregate([
 { $match: { $and: [{'Meta.FlowId' : UUID('ce5d9c36-68be-4d3d-95af-7904a9fab34a')}, 
                    {'Meta.UpstreamMessageId': {$ne: null}},
                    {'MessageType': {$ne: 'PublishDoneMessage'}}]}
 },
 { $group: {_id: '$Meta.TrackingId',
     all: {$sum: {$cond: [{$ne: ['$Status', 'ContainsInvalidEntities']}, 1, 0]}}, 
     processed: {$sum: {$cond: [{$and: [{$or: [{$eq: ['$Status', 'Processed']}, {$eq: ['$Status', 'Failed']}]}, {$ne: ['$HandleResult.HandleStatus', 'Deferred']}]}, 1, 0]}},
     failed: {$sum: {$cond: [{$eq: ['$Status', 'Failed']}, 1, 0]}},
     invalids: {$sum: {$cond: [{$or: [{$eq: ['$Status', 'ContainsInvalidEntities']}, {$eq: ['$HandleResult.HandleStatus', 'MissingSourceData']}]}, 1, 0]}}}
 },    
 { $group: {_id: null, 
      all: {$sum: 1},
      processed: {$sum: {$cond: [{$eq: ['$all', '$processed']}, 1, 0]}},
      failed: {$sum: {$cond: [{$ne: ['$failed', 0]}, 1, 0]}},
      invalids: {$sum: '$invalids'}}
 }
], {allowDiskUse: true})

Result from explain:

/* 1 */
{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "$and" : [ 
                        {
                            "Meta.FlowId" : UUID("ce5d9c36-68be-4d3d-95af-7904a9fab34a")
                        }, 
                        {
                            "Meta.UpstreamMessageId" : {
                                "$ne" : null
                            }
                        }, 
                        {
                            "MessageType" : {
                                "$ne" : "PublishDoneMessage"
                            }
                        }
                    ]
                },
                "fields" : {
                    "HandleResult.HandleStatus" : 1,
                    "Meta.TrackingId" : 1,
                    "Status" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "Caps.FlowMessageInfo",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "Meta.FlowId" : {
                                    "$eq" : UUID("ce5d9c36-68be-4d3d-95af-7904a9fab34a")
                                }
                            }, 
                            {
                                "MessageType" : {
                                    "$not" : {
                                        "$eq" : "PublishDoneMessage"
                                    }
                                }
                            }, 
                            {
                                "Meta.UpstreamMessageId" : {
                                    "$not" : {
                                        "$eq" : null
                                    }
                                }
                            }
                        ]
                    },
                    "queryHash" : "C55B39EF",
                    "planCacheKey" : "896E0A8A",
                    "winningPlan" : {
                        "stage" : "PROJECTION_DEFAULT",
                        "transformBy" : {
                            "HandleResult.HandleStatus" : 1,
                            "Meta.TrackingId" : 1,
                            "Status" : 1,
                            "_id" : 0
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "Meta.FlowId" : 1,
                                "Meta.UpstreamMessageId" : 1,
                                "MessageType" : 1,
                                "Meta.TrackingId" : 1,
                                "Status" : 1,
                                "HandleResult.HandleStatus" : 1
                            },
                            "indexName" : "Meta.FlowId_1_Meta.UpstreamMessageId_1_MessageType_1_Meta.TrackingId_1_Status_1_HandleResult.HandleStatus_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "Meta.FlowId" : [],
                                "Meta.UpstreamMessageId" : [],
                                "MessageType" : [],
                                "Meta.TrackingId" : [],
                                "Status" : [],
                                "HandleResult.HandleStatus" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "Meta.FlowId" : [ 
                                    "[UUID(\"ce5d9c36-68be-4d3d-95af-7904a9fab34a\"), UUID(\"ce5d9c36-68be-4d3d-95af-7904a9fab34a\")]"
                                ],
                                "Meta.UpstreamMessageId" : [ 
                                    "[MinKey, undefined)", 
                                    "(null, MaxKey]"
                                ],
                                "MessageType" : [ 
                                    "[MinKey, \"PublishDoneMessage\")", 
                                    "(\"PublishDoneMessage\", MaxKey]"
                                ],
                                "Meta.TrackingId" : [ 
                                    "[MinKey, MaxKey]"
                                ],
                                "Status" : [ 
                                    "[MinKey, MaxKey]"
                                ],
                                "HandleResult.HandleStatus" : [ 
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : []
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 994520,
                    "executionTimeMillis" : 13026,
                    "totalKeysExamined" : 994523,
                    "totalDocsExamined" : 0,
                    "executionStages" : {
                        "stage" : "PROJECTION_DEFAULT",
                        "nReturned" : 994520,
                        "executionTimeMillisEstimate" : 358,
                        "works" : 994523,
                        "advanced" : 994520,
                        "needTime" : 2,
                        "needYield" : 0,
                        "saveState" : 8039,
                        "restoreState" : 8039,
                        "isEOF" : 1,
                        "transformBy" : {
                            "HandleResult.HandleStatus" : 1,
                            "Meta.TrackingId" : 1,
                            "Status" : 1,
                            "_id" : 0
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 994520,
                            "executionTimeMillisEstimate" : 107,
                            "works" : 994523,
                            "advanced" : 994520,
                            "needTime" : 2,
                            "needYield" : 0,
                            "saveState" : 8039,
                            "restoreState" : 8039,
                            "isEOF" : 1,
                            "keyPattern" : {
                                "Meta.FlowId" : 1,
                                "Meta.UpstreamMessageId" : 1,
                                "MessageType" : 1,
                                "Meta.TrackingId" : 1,
                                "Status" : 1,
                                "HandleResult.HandleStatus" : 1
                            },
                            "indexName" : "Meta.FlowId_1_Meta.UpstreamMessageId_1_MessageType_1_Meta.TrackingId_1_Status_1_HandleResult.HandleStatus_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "Meta.FlowId" : [],
                                "Meta.UpstreamMessageId" : [],
                                "MessageType" : [],
                                "Meta.TrackingId" : [],
                                "Status" : [],
                                "HandleResult.HandleStatus" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "Meta.FlowId" : [ 
                                    "[UUID(\"ce5d9c36-68be-4d3d-95af-7904a9fab34a\"), UUID(\"ce5d9c36-68be-4d3d-95af-7904a9fab34a\")]"
                                ],
                                "Meta.UpstreamMessageId" : [ 
                                    "[MinKey, undefined)", 
                                    "(null, MaxKey]"
                                ],
                                "MessageType" : [ 
                                    "[MinKey, \"PublishDoneMessage\")", 
                                    "(\"PublishDoneMessage\", MaxKey]"
                                ],
                                "Meta.TrackingId" : [ 
                                    "[MinKey, MaxKey]"
                                ],
                                "Status" : [ 
                                    "[MinKey, MaxKey]"
                                ],
                                "HandleResult.HandleStatus" : [ 
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 994523,
                            "seeks" : 3,
                            "dupsTested" : 0,
                            "dupsDropped" : 0
                        }
                    }
                }
            }
        }, 
        {
            "$group" : {
                "_id" : "$Meta.TrackingId",
                "all" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$ne" : [ 
                                    "$Status", 
                                    {
                                        "$const" : "ContainsInvalidEntities"
                                    }
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                },
                "processed" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$and" : [ 
                                    {
                                        "$or" : [ 
                                            {
                                                "$eq" : [ 
                                                    "$Status", 
                                                    {
                                                        "$const" : "Processed"
                                                    }
                                                ]
                                            }, 
                                            {
                                                "$eq" : [ 
                                                    "$Status", 
                                                    {
                                                        "$const" : "Failed"
                                                    }
                                                ]
                                            }
                                        ]
                                    }, 
                                    {
                                        "$ne" : [ 
                                            "$HandleResult.HandleStatus", 
                                            {
                                                "$const" : "Deferred"
                                            }
                                        ]
                                    }
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                },
                "failed" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$eq" : [ 
                                    "$Status", 
                                    {
                                        "$const" : "Failed"
                                    }
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                },
                "invalids" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$or" : [ 
                                    {
                                        "$eq" : [ 
                                            "$Status", 
                                            {
                                                "$const" : "ContainsInvalidEntities"
                                            }
                                        ]
                                    }, 
                                    {
                                        "$eq" : [ 
                                            "$HandleResult.HandleStatus", 
                                            {
                                                "$const" : "MissingSourceData"
                                            }
                                        ]
                                    }
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                }
            }
        }, 
        {
            "$group" : {
                "_id" : {
                    "$const" : null
                },
                "all" : {
                    "$sum" : {
                        "$const" : 1.0
                    }
                },
                "processed" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$eq" : [ 
                                    "$all", 
                                    "$processed"
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                },
                "failed" : {
                    "$sum" : {
                        "$cond" : [ 
                            {
                                "$ne" : [ 
                                    "$failed", 
                                    {
                                        "$const" : 0.0
                                    }
                                ]
                            }, 
                            {
                                "$const" : 1.0
                            }, 
                            {
                                "$const" : 0.0
                            }
                        ]
                    }
                },
                "invalids" : {
                    "$sum" : "$invalids"
                }
            }
        }
    ],
    "serverInfo" : {
        "host" : "ip-10-93-178-93.us-west-2.compute.internal",
        "port" : 27017,
        "version" : "4.2.12",
        "gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1693474550, 6),
        "signature" : {
            "hash" : { "$binary" : "DLFvEwE9WWem+6i/CKdJchM/hbc=", "$type" : "00" },
            "keyId" : NumberLong(7217799378239488001)
        }
    },
    "operationTime" : Timestamp(1693474550, 6)
}

Do you have any idea how i can cover this query? I assume that the low performance of this query is caused by disk swapping that could be avoided by query covering.
Thanks.

As the documentation on the covering aggregation queries is non existent, could you please provide me with some courses/books that i could read on this matter? Or some another source of knowledge?