Aggregation with lookup slow

Working with Data
1

i wrote an aggregation query the first match stage filtered the result to 27000 records.i get this data under 1 second. then i have a lookup stage. both of the lookup primary and foreign keys are indexed. but i am getting result only after 3 seconds. 3 seconds is not a big time but i felt there is something behind the slowness as this is a simple query. do lookups do a colscan even if its indexed? is there some techniques i can use to speedup my lookups?

2

here is what i find when i print the explain command

{
			"$lookup" : {
				"from" : "od_class",
				"as" : "od_class",
				"localField" : "class_id",
				"foreignField" : "id",
				"unwinding" : {
					"preserveNullAndEmptyArrays" : false
				}
			},
			"totalDocsExamined" : 80368,
			"totalKeysExamined" : 80368,
			"collectionScans" : 0,
			"indexesUsed" : [ "id_1" ],
			"nReturned" : 80368,
			"executionTimeMillisEstimate" : 5167
		},
3

it turns out that the slowness is not from lookup it comes from unwind . without unwind operation query only takes 1 second to complete

4

This? https://www.mongodb.com/docs/manual/reference/operator/aggregation/unwind/

It compiles all array elements to a document for each. the output can be much more than without the unwind stage.

1 Like
5

yes its slow, since it have 84000 records i guess. but i need to unwind it due to a group stage i have to do after that