To troubleshoot slow queries, useQuery Explainto obtain the query executionplan and the runtime execution profile. The following section describesteps you can take to optimize query performance depending on the execution profile:

Limit the number of results

Use the records returned field in the execution tree toidentify if the query is returning many documents. Consider limiting the numberof documents returned by using the$limitclause. This reduces the serialized byte size of the results when returned tothe clients over the network. In caseswhere theLimit node is preceded by aMajorSort node, the query engine cancoalesce theLimit and theMajorSort nodes and replaces a full in-memorymaterialization and sort with a TopN sort, reducing the memory requirement forthe query.

Limit the Result Document Size

Consider limiting the size of thedocument returned by using the$project clause to avoid fetchingunnecessary fields. This helps reduce the compute and memory cost of processingintermediate results and the serialized byte size of the results when returnedto the clients over the network. In cases where all fields referenced in thequery are covered by a regular index (not multikey), this also allows the query to befully covered by the index scan, avoiding the need to fetch documents from theprimary storage.

Use indexes

Use the following instructions to set up and optimize indexes.

Identify if the query is using an index

You can identify if the query is using an index by checking the leaf nodes in theexecution tree. If the leaf node of the execution tree is aTableScan node,that means the query is not using an index and is scanning documents from primarystorage. If an index is being used, the leaf node of the execution tree willdisplay the index ID and index fields of the index.

Identify if the index used can be optimized

An index is useful for a query if it can reduce the number ofdocuments that the query engine needs to fetch from primary storage or if itsfield ordering can deliver the Sort requirement of the query.

If an index is used for a query, but the query engine is still fetching anddiscarding many documents, as identified by a Scan node that returns manyrecords followed by aFilter nodethat returns few records, this is a sign thatthe query predicate satisfied using the index is not selective. To create a moresuitable index, seeCreate indexes.

If a non-multikey index is used for a query, but the query engine is stillperforming an in-memory reordering of the result set, as identified by aMajorSort nodein the query execution tree, this is a sign that the index usedcan't be used to deliver the Sort requirement of the query. To create a moresuitable index, see the next section.

Index for$lookup

To improve the performance of a$lookup stage, create an index on theforeignField in thefrom collection. This allows the join operation to efficiently find matching documents in thefrom collection without scanning the entire collection.

Create Indexes

Follow the index management documentation tocreate indexes.To ensure your query can use indexes, create regular (not Multikey) indexeswith fields in the following order:

1. All fields that will be used in equality operators.To maximize chance of reuseacross queries, order fields in decreasing order of occurrence of the fields inequality operators among queries.
2. All fields that will be sorted on (in the sameorder).
3. Fields that will be used in range or inequality operators in decreasingorder of query constraint selectivity.
4. Fields that will be returned as part of aquery in the index: including such fields in the index allows the index to coverthe query and avoid having to fetch document from the primary storage.

For queries that involve filtering and sorting array fields, consider creatingMultikey indexes.

Use query hint

If you have created a more suitable index forthe query but the query engine is not using that index, you can overridethe query engine's index preference by using a query hint.

For more information on the output of a query executed with Query Explain, seeQuery execution reference.