[SERVER-22622] Improve $lookup explain to indicate query plan on the "from" collection Created: 16/Feb/16 Updated: 30/Jan/24 |
|
| Status: | Needs Scheduling |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Sergey Kazankov | Assignee: | Chris Harris |
| Resolution: | Unresolved | Votes: | 51 |
| Labels: | SWDI, query-product-scope-2, query-product-urgency-2, query-product-value-2 | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||
| Assigned Teams: |
Query Execution
|
||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||
| Description |
| Comments |
| Comment by Eric Sedor [ 07/Aug/23 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
601290552@qq.com For what you are reporting we'd like to encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums. If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project. Unless you find solid evidence to suggest it is the issue described by this ticket, it would be a new ticket. Sincerely, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by jing xu [ 04/Aug/23 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
hi Christopher Harris: , } }, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Viktor Molokostov [ 11/Nov/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thanks @christopher.harris@mongodb.com , it looks like my client (IntellijIDEA) is a bit limited in this matter, I'll try to explore it more with Compass. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Chris Harris [ 09/Nov/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Beginning in version 5.0, explain output has been meaningfully enhanced when it comes to $lookup via
This significantly improves the diagnosability and usability of the information related to $lookup directly reported by explain. We will revisit this request to further enhance this output at a later date when we make broader changes to the explain command. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Viktor Molokostov [ 08/Nov/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Are there any plans to add lookup stats into aggregation explain? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 11/Nov/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The same should apply to $graphLookup stage. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Max Hirschhorn [ 14/Mar/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi kazankov, I've converted this ticket into an improvement request for the explain output of the $lookup stage. Thanks for bringing this issue to our attention! Best, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Max Hirschhorn [ 17/Feb/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
There isn't a way to run an explain on an aggregation pipeline and see what index is being used to satisfy the $lookup stage. What I was trying to get at by saying
is that it's possible to run a query of the aforementioned form against the <from> collection to see what query plan would be used to answer it. As I showed in my previous comment, we will indeed use the {good: 1} index on the test.goods_persons collection. If you need more convincing, then we can also examine the plan summary output written to the logs while executing the aggregation pipeline from the ticket's description (with explain=false and setting the mongod's log level to 1).
We see two queries on the test.goods_persons collection: {good: {$eq: 1.0}} and {good: {$eq: 2.0}}, both of which are using the {good: 1} index on the test.goods_persons collection. This is precisely what we should expect given my description of how the $lookup stage is implemented and the explain output I showed earlier.
As you alluded to in this ticket's description, the aggregation pipeline is doing a collection scan of the test.goods collection. This means that every purchase will be examined, regardless of who made it. Since you've now said that you're only interested in the goods purchased by person #1, why not change your aggregation pipeline to run against the test.goods_persons collection and apply a filter to process only those goods purchased by person #1? From the explain output, we can verify that the following aggregation pipeline will use the {person: 1} index on the test.goods_persons collection to examine only person #1's purchases. The information for the goods purchased will then be looked up (individually, per the $lookup stage) by doing queries of the form {id: {$eq: <good>}} on the test.goods collection. These queries will be satisfied by using the {id: 1} index on the test.goods collection.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergey Kazankov [ 16/Feb/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
There is no lookup query in your example. My code is just for test. The real problem for me that 'goods' collection count is 10 millions and 'persons' count is 20. I just wanted to find goods for one person but with personal information. I used lookup + match like I used join in sql. And in mongo this query took a very-very long time. And result count was about 10 goods. Thats why I desided that no index was used. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Max Hirschhorn [ 16/Feb/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The COLLSCAN in the explain output is referring to the scan of the test.goods collection (i.e. the collection the "aggregate" command is being run on) and doesn't mean an index isn't being used to answer the $lookup. The $lookup stage simply executes a query of the form {<foreignField>: {$eq: <localField value>}} on the <from> collection for each source document. Running the "explain" command on the test.goods_persons collection after having created an index {good: 1}, we can see that the $lookup stage will indeed use that index.
|