3.1 sort memory limit

In the notes, it said the sort mem max is 32MB. But the latest document said 100 MB ?

Hi @Jie_Long_15309,

In the video lecture it is mentioned that for sorting without using indexes, the server will read the documents from the disk into RAM and then in RAM it will perform sorting algorithm on the documents. In this case the combined size of all the documents in the sort operation must be less than 32 MB.

Moreover, the $sort stage has the limit of 100 MB of RAM. You can refer to the following documentation for more details:

Please let me know, if you have any questions.

Thanks,
Sonali

Thanks. Please clarify more.

Dose this mean the following ?

  1. Using index, $sort stage limit is 100 MB
  2. Without using index, $sort stage limit is 32 MB ram

Hi @Jie_Long_15309,

Firstly, lets talk about sorting without using Indexes:
If we do not use indexes for a sort operation, then the query will retrieve the documents and then sort the results in the memory. In this case, sorting will abort when they use 32 megabytes of memory.

Now, if we have to perform sorting on the indexed fields, MongoDB will use index to retrieve the documents in the order specified by the sort. Here we do not need to worry about the memory restriction as we can obtain the sort order by retrieving documents based on the ordering in an index. So, we do not need to sort the results in the memory.

Lastly, $sort is an aggregation pipeline stage and it sorts all input documents and returns them to the pipeline in sorted order. All the pipeline stages (including $sort stage) has a limit of 100 MB of RAM. Moreover, this is the limit for documents in the $sort stage and not the memory restriction for sorting operation.
Also, as mentioned in the documentation, $sort operator can take advantage of an index when placed at the beginning of the pipeline or placed before the $project , $unwind , and $group aggregation operators. If $project , $unwind , or $group occur prior to the $sort operation, $sort cannot use any indexes.

I hope it helps!!

Please let me know, if you have any questions.

Thanks,
Sonali

1 Like

Thanks for the explanation. More follow-up questions. How about these following cases ?

  1. If query/aggregation stage is a covering index, but the sorting cannot leverage the index ?
  2. If query/aggregation stage is doing sorting on multiple fields. Assuming the index covers one or none of the sorted fields ?

Hi @Jie_Long_15309,

Let me check this and get back to you.

Thanks,
Sonali

Hi @Jie_Long_15309,

For Example: If I have a collection containing documents with two fields a and b like below:
12%20PM
Here, if we create index on field a and then issue the following query:
> db.test_col.find({a:1}).sort({b:1})

Then the query will first fetch the documents corresponding to the filter ({a:1}) and then it will sort the fetched documents in the RAM.
Also, note that the above query is not Covered Query because there is no index on b field.

If we are sorting on multiple fields and one or more fields are not part of index, then the sorting will happen in memory. You can check the screenshot below:

Please refer to the following doc for more details:

Please let me know, if you have any questions.

Thanks,
Sonali

1 Like

How about even it’s covering index, but the index sorting order asc/dsc does not match the query ? I assume this also uses memory sorting and subjects to 32 MB limit ?

Hi @Jie_Long_15309,

If a query is covered query and we need to sort on indexed fields, then sort operations use the index prefixes to sort the results. These operations do not need to sort the result set in memory.

For example:

For the Index prefix: { a: 1, b: 1 }, the following query will use index prefix (not in-memory sort) to sort for fields a and b in descending order :

db.data.find().sort( { a: -1, b: -1 } )

Please let me know, if you have any questions.

Thanks,
Sonali