"Query Failed" error when trying to sort big collections

Let’s say I have a collection with 1,3 million documents and I want to see the most recent created documents. How do I do that?
I tried sorting by { “createdAt”: -1 } and got that error:
Yes, I can filter by createdAt > 2022-09-03T11:53:28.419+0000 to create a smaller data set and sort this subset but isn’t there a way to achieve this goal quicker / with the full collection?
Or is this some kind of database server config that prevents such big operations?

That’s coming from the database. You should look at enabling “AllowDiskUse” in the Aggregation Options tab

or if it was actually a Query, in the Find Query Options on the Collections Tab


1 Like

I knew about “Allow disk use” in aggregations but these Query Options are new to me and enabling Allow disk use in here did the trick! The entire collection got sorted in just a few seconds. :exploding_head:
Does this option provide a (small?) speed boost for all queries on bigger collections or does it only kick in when I usually would get a real error like shown above? If that would speed up queries in general, is it possible to “auto-enable” that feature for all collection tabs?

It should only kick in when you exhaust the allocated memory for queries. The draw-back is that the server’s IO will then be being consumed by managing the data in and out on disk for the query. Sounds like you found the sweet spot (and/or your server is very RAM constrained)

I think a short-time IO usage will be okay, these few times. Our software runs on multiple Kubernetes clusters with dozens of nodes, CPU/memory/disk space is dynamic, there’s a limit of nearly 5TB memory for all nodes and we’re not even using a third of this currently. So I don’t know how a database operation can even run out of memory in such environments but that’s way out of my business, so I’m fine with this workaround for now :slight_smile:

It’s a hard 100MB limit on memory consumption (and I believe still not configurable). That’s how you get ants/run out of memory.

1 Like