Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-33526

Count performance when using a join

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 3.6.3
    • Component/s: Aggregation Framework
    • None

      Please note this isn't https://jira.mongodb.org/browse/SERVER-31760

      Performing a `count` on a largish collection doesn't complete in what would be considered a reasonable time. Running this query won't complete (> 10 mins before giving up).
      Venues is 500k objects, Tasks is 15mil. Indexes are in place to support this query.

      db.venues.aggregate(
      	[
      		{
      			$match: {
      			
      			}
      		},
      		{
      			$lookup: {
      			    "from" : "tasks",
      			    "as" : "foreign",
      			    let: { localField: "$_id"},
      			    "pipeline":[
      						{
      							"$match":{
      								"$expr":{
      									"$eq":["$v","$$localField"]
      								}
      							}
      						},
      						{"$limit":1},
      						{"$count":"count"}
      					]
      			}
      		},
      		{
      			$match: {"foreign.count":1}
      		},
      		{
      			$count: "count"
      		},
      	],
      
      	// Options
      	{
      		cursor: {
      			batchSize: 50
      		}
      	}
      );
      

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            henry@live.xyz henry clifford
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: