Best index on aggregation with multiple match conditions

[Francesco_Fiorentino]

@Asya_Kamsky

However here the best index isn’t being used. It doesn’t look like you have an index on the two fields you are querying on (STATE and countryCode).

That index should be there, below the output of getIndexes() but it seems to be mentioned also on rejectedPlans: I didn’t understand why it is not the winning one.

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_"
    },
    {
        "v" : 2,
        "key" : {
            "__STATE__" : 1,
            "createdAt" : -1
        },
        "name" : "state_createdAt",
        "background" : false
    },
    {
        "v" : 2,
        "key" : {
            "__STATE__" : 1,
            "success" : 1
        },
        "name" : "state_success",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "tagUid" : 1
        },
        "name" : "tagUid",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "__STATE__" : 1,
            "countryCode" : 1
        },
        "name" : "state_countryCode"
    }
]

@Asya_Kamsky

This query is aggregating over two million documents - even using an efficient index processing that many documents is going to take time.

This means, as I supposed previously, that on demand aggregation is not to be used in this case? What is the best approach to use? It is ok to set periodic aggregation with output on another collection or there is some approach more effective?