Help with correct index's on collection to prevent Query Targeting: Scanned Objects / Returned has gone above 1000 alert

Working with Data
,
1

Hello,

I have an aggregation query like the below on a collection of bookings, bookings have a days property which is an array of days, each day has a start and end date. The aggregation query returns all bookings where some of the days fall between a start and end date (start, end dates come from request payload). Every time this query is ran however I receieve an alert that Query Targeting: Scanned Objects / Returned has gone above 1000 but I canno’t seem to get the right index’s to help prevent this, I have also tried lots of variations of the below query all with no success.

const match = {
		status: 1,
		customer: new Types.ObjectId(customer),
	};

	// add filters to match query if they exist in request payload
	if (userFilter) match.assignee = userFilter.assignee;
	if (clientFilter) match.client = clientFilter.client;
	if (typeFilter) match.type = typeFilter.type;

Booking.aggregate([
			{
				$match: {
					...match,
					days: {
						$elemMatch: {
							// filter bookings by start and end date
							start: {
								$gte: new Date(start).toISOString(),
								$lte: new Date(end).toISOString(),
							},
						},
					},
				},
			},
			{
				$sort: { createdAt: 1 },
			}
])

Any help would be greatly appreciated!

3

Hi @Tim_Horwood,

Every time this query is ran however I receieve an alert that Query Targeting: Scanned Objects / Returned has gone above 1000 but I canno’t seem to get the right index’s to help prevent this, I have also tried lots of variations of the below query all with no success.

On top this, have you also run the db.collection.explain("executionStats") against the query(s) to verify that these are the offending queries? If there are other queries running at the same time, the alert may be caused by another query but this would help round it down.

// add filters to match query if they exist in request payload

I’m curious about this particular section. Depending on the request payload contents, the most appropriate index could change. Do you have a few indexes on this collection already? If so, can you provide the output of db.collection.getIndexes()?

I have also tried lots of variations of the below query all with no success.

In addition to the requested information above, can you please provide:

  1. The MongoDB version in use
  2. The output of db.collection.explain("executionStats") on the query you believe is creating the alert
  3. Sample document(s) from the collection

In addition to the above, perhaps the following may help:

Regards,
Jason