Poor aggregation performance

Question

I have two collections

Posts:

{
    "_Id": "1",
    "_PostTypeId": "1",
    "_AcceptedAnswerId": "192",
    "_CreationDate": "2012-02-08T20:02:48.790",
    "_Score": "10",
    ...
    "_OwnerUserId": "6",
    ...
},
...

and users:

{
    "_Id": "1",
    "_Reputation": "101",
    "_CreationDate": "2012-02-08T19:45:13.447",
    "_DisplayName": "Geoff Dalgas",
    ...
    "_AccountId": "2"
},
...

and I want to find users who write from 5 to 15 posts. This is what my query looks like:

db.posts.aggregate([
    {
        $lookup: {
            from: "users", 
            localField: "_OwnerUserId",
            foreignField: "_AccountId", 
            as: "X"
        }
    },  
    {
        $group: {
            _id: "$X._AccountId", 
            posts: { $sum: 1 }
        }
    },   
    {
        $match : {posts: {$gte: 5, $lte: 15}}
    },  
    {
        $sort: {posts: -1 }
    },
    {
        $project : {posts: 1}
    }
])

and it works terribly slowly. For 6k users and 10k messages, it took more than 40 seconds to get a response, while in a relational database I get a response in a second. Where is the problem? I am just starting with mongodb, and it is possible that I messed up this request.

Answer 1

from https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

foreignField . $lookup foreignField. localField . foreignField, $lookup null .

, .

_AccountId, 10 000 . .

db.users.ensureIndex("_AccountId", 1) 

, 10 000 10 000 .

Answer 2

bauman.space _accountId ( ), $ match (.. ). - ( ), $ lookup (join).

, , , ( ) . 60 !

" " MongoDB.

Answer 3

Use $matchthen $lookup. Filter $matchstrings should be checked for $lookup. It is effective.