Sort exceeded memory limit

Hello. I’m hitting a wall with this query. I have a “regular” index on the only column I’m sorting on, and I keep getting the following error. Using disk seems like an extreme measure on a query that is only supposed to return 1,000 records. Can anyone help?

MongoDB\Driver\Exception\CommandException: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

I’m noticing this coming from my ORM. I’m using Laravel…

customers.aggregate([{"$match":{"project_id":{"$eq":"5da925f416f09b7b977d7583"},"deleted_at":{"$eq":null}}},{"$addFields":{"session.last_seen_at":{"$convert":{"input":"$session.last_seen_at","to":"date","onError":{"$convert":{"input":{"$multiply":["$session.last_seen_at",1000]},"to":"date","onError":"$session.last_seen_at"}}}}

This conversion on the field i’m sorting on, I assume probably negates the index on it.

Hello @Scott_Weiner,

What are you sorting on? Your aggregation doesn’t show any sort operation. Also, see Aggregation $sort Stage and Memory, and it says:

The $sort stage has a limit of 100 megabytes of RAM. By default, if the stage exceeds this limit, $sort will produce an error. To allow for the handling of large datasets, set the allowDiskUse option to true to enable $sort operations to write to temporary files.

Remove the sort and check that the number of records being returned is actually 1000. What is the size of each record? You may be able to use $project to reduce the size of records being retuned.

I was sorting on “session.last_seen_at”, but I was able to remove it because it wasn’t needed in the query, and that solved it. Thanks.

Good call with $project, I didn’t realize that could save time on queries. There is a lot to learn about query optimization. I hit a number of issues trying to send emails out to thousands of people. my IOPs were exceeding 300/s.