CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate
HRK44
HRK44

Reputation: 2742

MongoDB aggregate slow with indexed match?

I have a collection A and some fields a,b,c,d...

If I do :

db.A.aggregate([{$match: {a: true, b: false, c: false}, {$limit: 50}]) >> this is fast (0.1s)

Now if I do

db.A.aggregate(
        [
            {
                $lookup: {
                    from: 'B',
                    localField: 'b',
                    foreignField: '_id',
                    as: 'b'
                }
            },
            {$match: {'b.d': true}},
            { $limit: 50 }
        ]
    )

This takes about 1.5s (lot of documents, I'm OK with 1.5s)

Now if I do the same as before, but just adding the $match (which should use indexes...) :

db.A.aggregate(
        [
            {$match: {a: true, b: false, c: false}},
            {
                $lookup: {
                    from: 'B',
                    localField: 'b',
                    foreignField: '_id',
                    as: 'b'
                }
            },
            {$match: {'b.d': true}},
            { $limit: 50 }
        ]
    )

This takes 10s ? I'm very confused why.

PS: I have index on all those fields.

Upvotes: 0

Views: 277

Answers (2)

nixxo_raa
nixxo_raa

Reputation: 402

In your case which is the last one ($match, $lookup, $match) you are using $match stage two times. where as in other cases you are joining different collections and then displaying the matched documents. As you said you have indexed columns could you please tell me the value of the column whether it contain text, number etc.

Upvotes: 0

HRK44
HRK44

Reputation: 2742

Nevermind, I found the issue. I had an index on all the fields but one, which was slowing down my request.

I found it by using the {explain: true} option and I saw that it was using a compound index that didn't include one of the fields.

Now I'm good with my 1.5s query

Upvotes: 1

Related Questions