Details
-
Improvement
-
Resolution: Done
-
Major - P3
-
None
-
3.4.2
-
None
Description
I am trying to use mongoDb aggregation pipeline to lookup other table and check if matched some of the attributes.
my query is something like below (p.s. bigtable is sharded):
db.bigtable.aggregate( [
|
{$lookup:{ from: "user", localField: |
"user_code", foreignField: "code",as: "user"}}, |
{$match: {"user.country":"US" } }] ) |
I have created index for bigtable.user_code, user.code & user.country. But the query is very very very slow. I tried explain it, it is COLLSCAN.
It seems to me what using $match with foreign table's attribute, index is not supported and therefore, the query speed is very slow.