Hi @Kim_Hakseon,
A sort that is supported by an index only has to fetch matching results. In the context of an aggregation pipeline, this also allows a sort stage to be non-blocking: results can be passed to other stages for processing because documents are already returned in sorted order using the index.
If there isn’t an index to support a sort operation, an in-memory sort is required. In this case the server has to allocate a buffer in memory to temporarily store the search results and sort them in-place. This adds memory and computational overhead to a query and is a blocking stage for aggregation: further processing cannot happen until the results have been sorted.
There is a limit for in-memory sorts to mitigate potential resource usage for a large number of concurrent in-memory sorts (100MB in MongoDB 4.4 or 32MB in earlier server versions).
The aggregation framework has an allowDiskUse option which is also available for find queries in MongoDB 4.4+. This option allows data for a large in-memory sort to be written to temporary files (if necessary) rather than having the operation fail because of the in-memory sort limit.
However, the optimal approach is avoiding in-memory sorts where possible.
As a clarification for your description of “index on disk”, note that actively used indexes and documents are loaded into the working set in the WiredTiger internal cache which is 50% of (RAM-1GB) by default. Indexes are persisted to disk, but loaded into memory when used or updated.
Regards,
Stennie