[SERVER-31760] Lookup sub-pipeline is not using index for equality match Created: 29/Oct/17  Updated: 23/Aug/18  Resolved: 04/Jan/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.6.3, 3.7.1

Type: Bug Priority: Major - P3
Reporter: Naveen Assignee: David Storch
Resolution: Done Votes: 4
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MongoDB server version: 3.6.0-rc1


Issue Links:
Backports
Documented
is documented by DOCS-11203 Docs for SERVER-31760: Lookup sub-pip... Closed
Duplicate
is duplicated by SERVER-32826 Lookup Pipeline issue performance is ... Closed
is duplicated by SERVER-33364 Combined $match, $expr in aggregation... Closed
Related
related to SERVER-31804 $expr rewrite optimization can lead t... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v3.6
Sprint: Query 2017-11-13, Query 2017-12-04, Query 2017-12-18, Query 2018-01-01, Query 2018-01-15
Participants:
Linked BF Score: 0

 Description   

I am doing following aggregation to join two collection using foreign pipeline.
It seems match stage is not using index at all.

db.blogpost.aggregate([
  { $lookup: { 
    from: 'comment', 
    let: { blogpost_id: "$_id" }, 
    pipeline: [ { 
       $match: { $expr: { $eq: [ "$blogpost.id", "$$blogpost_id"] } } 
    } ], 
    as: 'comments'
  }}
])



 Comments   
Comment by Trevor Wilson [ 23/Aug/18 ]

I've found that $expr still does not use the correct index if it is a multi-key index. I've created a ticket here: SERVER-36818

Comment by Asya Kamsky [ 15/Apr/18 ]

Since this bug was fixed, aniket please open a new SERVER ticket indicating behavior which appears to be wrong. Please provide all information (exact aggregation, available indexes, relevant log lines).

Comment by aniket [ 14/Apr/18 ]

I am still facing same issue with current stable vaersion 3.6.3.

Comment by Githook User [ 01/Feb/18 ]

Author:

{'email': 'david.storch@10gen.com', 'name': 'David Storch', 'username': 'dstorch'}

Message: SERVER-31760 Optimize $expr to allow index use for equality predicates.

(cherry picked from commit 9282f7813d97756f0ab1e635d57e84b7145560d7)

Conflicts:
src/mongo/db/matcher/rewrite_expr.cpp
Branch: v3.6
https://github.com/mongodb/mongo/commit/6a67111af31e1352c43a821e10b7409e43205863

Comment by Githook User [ 01/Feb/18 ]

Author:

{'email': 'david.storch@10gen.com', 'name': 'David Storch', 'username': 'dstorch'}

Message: SERVER-31760 Add index support for InternalExprEqMatchExpression.

(cherry picked from commit 6699621bfb54174c7ee082ee85c62211788942c3)

Conflicts:
src/mongo/db/matcher/expression_internal_expr_eq.cpp
src/mongo/db/matcher/expression_internal_expr_eq.h
src/mongo/db/matcher/expression_internal_expr_eq_test.cpp
src/mongo/db/matcher/expression_leaf.cpp
src/mongo/db/matcher/expression_leaf.h
src/mongo/db/matcher/expression_parser.cpp
Branch: v3.6
https://github.com/mongodb/mongo/commit/779beeca39066f939dc4fdbcb05f2e6ed05e99fa

Comment by Githook User [ 01/Feb/18 ]

Author:

{'email': 'david.storch@10gen.com', 'name': 'David Storch', 'username': 'dstorch'}

Message: SERVER-31760 Add InternalExprEqMatchExpression.

(cherry picked from commit 01c39d416435f930d249701ccb71744b519873b5)

Conflicts:
src/mongo/db/matcher/expression_array.h
src/mongo/db/matcher/expression_leaf.h
src/mongo/db/matcher/expression_path.h
src/mongo/db/matcher/expression_type.h
src/mongo/db/matcher/path.cpp
src/mongo/db/matcher/path_test.cpp
src/mongo/db/matcher/schema/expression_internal_schema_eq.cpp
src/mongo/db/matcher/schema/expression_internal_schema_object_match.cpp
src/mongo/s/shard_key_pattern.cpp
Branch: v3.6
https://github.com/mongodb/mongo/commit/72267ebfbe177cb37f742397f633bc484ebf52c5

Comment by Githook User [ 04/Jan/18 ]

Author:

{'name': 'David Storch', 'username': 'dstorch', 'email': 'david.storch@10gen.com'}

Message: SERVER-31760 Optimize $expr to allow index use for equality predicates.
Branch: master
https://github.com/mongodb/mongo/commit/9282f7813d97756f0ab1e635d57e84b7145560d7

Comment by Githook User [ 29/Dec/17 ]

Author:

{'name': 'David Storch', 'username': 'dstorch', 'email': 'david.storch@10gen.com'}

Message: SERVER-31760 Add index support for InternalExprEqMatchExpression.
Branch: master
https://github.com/mongodb/mongo/commit/6699621bfb54174c7ee082ee85c62211788942c3

Comment by Githook User [ 18/Dec/17 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@10gen.com', 'username': 'dstorch'}

Message: SERVER-31760 Add InternalExprEqMatchExpression.
Branch: master
https://github.com/mongodb/mongo/commit/01c39d416435f930d249701ccb71744b519873b5

Comment by James Wahlin [ 28/Nov/17 ]

Hi Naveen,

We are working on fixing this issue on our development branch (which is what "3.7 Desired" reflects). Once a fix is in place we will evaluate for backport to the 3.6 stable branch, which if green-lighted would be delivered in one of the 3.6 minor releases. Please continue to watch this ticket for updates.

Thanks,
James

Comment by Naveen [ 28/Nov/17 ]

Thanks James! Will the fix be available in 3.6? What is "3.7 Desired"?

Comment by James Wahlin [ 06/Nov/17 ]

Hi Naveen,

Thanks for reporting this issue. My apologies for the silence on this ticket.

It is expected that $lookup with a foreign pipeline, which references a foreign dotted field path, will not use indexes in mongod version 3.6.0-rc1. This is the case when using $expr in a find command against a single collection as well. We recognize that this behavior is not ideal and are exploring the addition of index use for equality for 3.6.0, at least for the case where the foreign field is not of type array. We will use this ticket to track and will update when we have more information.

Thanks,
James

Generated at Thu Feb 08 04:28:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.