Native Hypernova using $lookup in MongoDB 3.6

[theodorDiaconu]

MongoDB 3.6 supports nesting and chaining $lookups. As soon as 3.6 gets released this is absolutely crucial to have. And allow both hypernova/aggregate lookup queries.

[theodorDiaconu]

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup

[theodorDiaconu]

I am shocked to discover that hypernova is 10x slower than Grapher for example:

db.getCollection('users').aggregate([
    {
        $project: { _id: 1 }
    },
    {
        $lookup: {
            from: "posts",
            as: "posts",
            let: { id: "$_id" },
            pipeline: [
                {
                    $project: { _id: 1, ownerId: 1 }
                },
                {
                    $match: {
                        $expr: {
                            $eq: ["$ownerId", "$$id"]
                        }
                    },
                },
                {
                    $lookup: {
                        from: 'comments',
                        as: 'comments',
                        let: { id: "$_id"},
                        pipeline: [
                            {
                                $project: { _id: 1, postId: 1 }
                            },
                            {
                                $match: {
                                   $expr: {
                                       $eq: ["$postId", "$$id"]
                                   }
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
]);

Takes 400ms. With Grapher it takes 19-23ms.

[Herteby]

Could it be that it's not using indexes?

[theodorDiaconu]

It's the same db, I've set it up locally. Why wouldn't it use index, $match should be smart enough maybe?

[crapthings]

yes $lookup is very slow

[davidbayo10]

Currently, new $lookup (3.6 version) is not using indexes.

New $lookup version (MongoDB >= 3.6)

db.getCollection('users').aggregate([
    {
        $project: { _id: 1 }
    },
    {
        $lookup: {
            from: 'posts',
            as: 'posts',
            let: { id: '$_id' },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ['$ownerId', '$$id']
                        }
                    },
                }
            ]
        }
    }
]);

Old $lookup version (MongoDB < 3.6)

db.getCollection('users').aggregate([
    {
        $project: { _id: 1 }
    },
    {
        $lookup: {
            from: 'posts',
            as: 'posts',
            localField: '_id',
            foreignField: 'ownerId'
        }
    }
]);

Last query uses indexes, the other one not

[theodorDiaconu]

Ok, so how would you run that whole query with that approach?

Regards, Theodor

On 2 Jun 2018, at 9:46 AM, David Bayo Alcaide [email protected] wrote:

Currently, new $lookup (3.6 version) is not using indexes.

New $lookup version (MongoDB >= 3.6)

db.getCollection('users').aggregate([ { $project: { _id: 1 } }, { $lookup: { from: 'posts', as: 'posts', let: { id: '$_id' }, pipeline: [ { $match: { $expr: { $eq: ['$ownerId', '$$id'] } }, } ] } } ]); Old $lookup version (MongoDB < 3.6)

db.getCollection('users').aggregate([ { $project: { _id: 1 } }, { $lookup: { from: 'posts', as: 'posts', localField: '_id', foreignField: 'ownerId' } } ]); Last query uses indexes, the other one not

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[davidbayo10]

With that approach it is difficult to solve that case, because you need a deep lookup. I think MongoDB should fix indexes problem in last $lookup version and your query will work perfectly.

[theodorDiaconu]

@davidbayo10 then it's irrelevant what you showed me. Another huge problem of $lookup is that it does not work with sharded collection, Grapher works with sharded collections.

[davidbayo10]

I just showed you two examples of $lookup. First one not uses indexes and the second one yes. But your first query is slow because of MongoDB issue with new $lookup version

[theodorDiaconu]

@davidbayo10 look here: https://docs.mongodb.com/manual/core/aggregation-pipeline/#aggregation-pipeline-operators-and-performance

It seems that if we apply projection after $match then it may work ? This is confusing a bit. Why would having $projection first disable the ability to use indexes, or maybe indexes can't be used when having $match with $expr