Aggregate operation is faster with hint although query planner chooses the same index

Working with Data
,
1

Hi everyone,

I have 3 node replicatSet with MongoDB 4.2.1. I’m working with 1.4 million records collection with several indexes. I’m doing an aggregate query to get the count of products with “PENDING” by organization. The aggregate query is this one:

db.products.aggregate([ { $match: { “productStatus” : “PENDING”, “organizationId” : { “$in”: [ “org6ENP” ] }}} , { $group: { “_id” : “$organizationId”, “n”: { $sum: 1 } } } ]);

I already have this index:

    	{
		"v" : 2,
		"key" : {
			"productStatus" : 1,
			"organizationId" : 1
		},
		"name" : "productstatus_org",
		"ns" : "data.products"
	}

This query works, but the result takes almost 10 seconds to return.

If I add a “hint” in the aggregate query, the same query returns the correct value almost instantly.

    db.products.aggregate([ { $match: { "productStatus" : "PENDING", "organizationId" : { "$in": [ "org6ENP" ] }}} , { $group: { "_id" : "$organizationId", "n": { $sum: 1 } } } ], { hint: "productstatus_org" })

I thought that the query planner was choosing a different index when running the query, but when adding “{ explain: true }” to both queries, the explain output is identical in both cases (same winningPlan, same nReturned, same totalKeysExamined, …)
Also, the same results are returned when adding the “allPlansExecution” option.

Why is this happening? Do I have to add the “hint” option to all my aggregates?

Thanks in advance,