Efficient group distinct count in MongoDB aggregation

user_2131907 0
Viewed 34

I have the following data and I would like to know How many account that has LogCounts >= 7

Account LogCounts
AAA 2
BBB 7
AAA 7
AAA 8
AAA 3
CCC 2

Here is my working MongoDB pipeline

[
    {
        '$match': {
            'LogCounts': {
                '$gt': 6
            }
        }
    }, {
        '$project': {
            'Account': 1
        }
    }, {
        '$group': {
            '_id': '$Account'
        }
    }, {
        '$count': 'FinalAccountCounts'
    }
]

But it took about 5 minutes for ~800 million records collection. I'd like to know if there's any better, faster or more efficient to solve this problem.

Thank you.

aggregation-framework pymongo mongodb-query mongodb
1 Answers

From a query perspective you are pretty much as efficient as you can be, this is under the assumption you have an index on LogCounts.

What you can try is to split your query into in different ranges of LogCounts values, this will require prior knowledge of your data distribution. But using this approach you can "map - reduce" the query results. This approach will not help much if the cardinality is extremely low, i.e LogCount has max value of 7 for example

Let's assume for a second LogCounts can only be in the range of 7 to 21. i.e in [7,8,9,10,...,20,21] with equal distribution for each value. In this case you could execute x queries each of them only querying a certain range of the values:

for example 5 queries at once will look like:

// in nodejs, all queries are executed at once.
const results = await Promise.all([
    db.collection.aggregate([ { match: { LogCounts: {$gt: 7, $lte: 10} }}, ...restOfPipeline]),
    db.collection.aggregate([ { match: { LogCounts: {$gt: 10, $lte: 13} }}, ...restOfPipeline]),
    db.collection.aggregate([ { match: { LogCounts: {$gt: 13, $lte: 16} }}, ...restOfPipeline]),
    db.collection.aggregate([ { match: { LogCounts: {$gt: 16, $lte: 19} }}, ...restOfPipeline]),
    db.collection.aggregate([ { match: { LogCounts: {$gt: 19, $lte: 21} }}, ...restOfPipeline]),
])

// now merge results in memory.

I can tell you that from testing this approach on a 800M collection on a dataset with string values and high cardinality my 1 query ran in 3min, when I split it to 2 queries I managed to run it in 2.1min including the "reduce" part in memory.

Obviously optimization this will require trial an error as you have several parameters to consider, # of buckets, value cardinality for query optimization ( one query can be 7 to 10 and one query 10 to 21 depending on distribution ), # of results etc

If you do end up choosing my approach I'd be happy to get an update after some testing.

user_107703700
Related