Sort limit reached for field which has an index

Question

I am trying to sort cursor by two fields "start" and "end". Both of them have indexes.

This is the code attempting to sort.

    DBCursor cursor = store.colConcepts.find(q);
    cursor.addOption(Bytes.QUERYOPTION_NOTIMEOUT);
    BasicDBObject sortObj = new BasicDBObject( "start", filter.isEventTimeSortDirAscending() ? 1 : -1 ).append( "end", filter.isEventTimeSortDirAscending() ? 1 : -1 ); 
    cursor = cursor.sort( sortObj );

In above code query q is { "tags" : { "$all" : [ "Person"]}}

And following are the indices on collection store.colConcepts.

        colConcepts.ensureIndex(new BasicDBObject("tags", 1));
        colConcepts.ensureIndex(new BasicDBObject("roles.concept",1));
        colConcepts.ensureIndex(new BasicDBObject("keys",1));
        colConcepts.ensureIndex(new BasicDBObject("start", 1));
        colConcepts.ensureIndex(new BasicDBObject("end", 1));

Following is the result of cursor.explain().

{ "cursor" : "BtreeCursor tags_1" , "isMultiKey" : true , "n" : 237267 , "nscannedObjects" : 237267 , "nscanned" : 237267 , "nscannedObjectsAllPlans" : 237267 , "nscannedAllPlans" : 
237267 , "scanAndOrder" : false , "indexOnly" : false , "nYields" : 1853 , "nChunkSkips" : 0 , "millis" : 274 , "indexBounds" : { "tags" : [ [ "Person" , "Person"]]} , "allPlans" : [ 
{ "cursor" : "BtreeCursor tags_1" , "isMultiKey" : true , "n" : 237267 , "nscannedObjects" : 237267 , "nscanned" : 237267 , "scanAndOrder" : false , "indexOnly" : false , 
"nChunkSkips" : 0 , "indexBounds" : { "tags" : [ [ "Person" , "Person"]]}}] , "server" : "xxx:27017" , "filterSet" : false , "stats" : { "type" : "FETCH" , "works" : 237269 , 
"yields" : 1853 , "unyields" : 1853 , "invalidates" : 0 , "advanced" : 237267 , "needTime" : 1 , "needFetch" : 0 , "isEOF" : 1 , "alreadyHasObj" : 0 , "forcedFetches" : 0 , 
"matchTested" : 0 , "children" : [ { "type" : "IXSCAN" , "works" : 237268 , "yields" : 1853 , "unyields" : 1853 , "invalidates" : 0 , "advanced" : 237267 , "needTime" : 1 , 
"needFetch" : 0 , "isEOF" : 1 , "keyPattern" : "{ tags: 1 }" , "isMultiKey" : 1 , "boundsVerbose" : "field #0['tags']: [\"Person\", \"Person\"]" , "yieldMovedCursor" : 0 , 
"dupsTested" : 237267 , "dupsDropped" : 0 , "seenInvalidated" : 0 , "matchTested" : 0 , "keysExamined" : 237267 , "children" : [ ]}]}}

As you can see tags,start, end all of them have indices.

Upon execution it is producing the exception :

com.mongodb.MongoException: Runner error: Overflow sort stage buffered data usage of 33554442 bytes exceeds internal limit of 33554432 bytes

I did some research on the issue and found that this problem can come up if you have no index on the field. or if the fields are indexed as sparse which is not the case in situation I have.

I am using mongodb 2.6.1. I did run the code with 2.6.4 but that didn't stop mongo from throwing exception.

Any idea how this can be solved?

if you could run an explain an attach the output that could help to resolve the problem. If you use an older version of mongodb than the 2.6 there is no index intersection so one query can only use one index. But you can make a compound index with both fields, in the exact order you would like to use it to sort. — attish, Oct 03 '14 at 12:07
@attish For the record. Earlier releases are able to use another index to "sort" by. But indeed the error points to no index being selected for the sort. So more information on the indexes present and the "query" itself along with `.explain()` output would be required. — Neil Lunn, Oct 03 '14 at 12:18
@Neil: Can you link some documentation around this to use another index to sort by? This sounds new. — attish, Oct 03 '14 at 12:59
@attish Not new and I have had plenty of discussions around it. Put simply `$or` queries and `.sort()` operations have had access to multiple indexes (but not otherwise as an "intersection" ) since MongoDB 2.2, from memory ( someone please correct this if not true ). In lieu of a definitve resource right now, but I also think the "main topic" of this question is asking for current index information and `.explain()` output as we have both asked. If you want more information on the query optimizer, then post your own question. Someone will get to it sooner or later. — Neil Lunn, Oct 03 '14 at 13:08
@Neil:Do not feel offended. :) I have not thougth about this, but you are just halfway right, so in 2.6: http://docs.mongodb.org/manual/reference/operator/query/or/#or-and-sort-operations so this is new in 2.6. in earlier versions either the individual clauses supported by the index or the sort itself : http://docs.mongodb.org/v2.4/reference/operator/query/or/ Feel free to ask as one would say. — attish, Oct 03 '14 at 13:26
@attish Not offended. But just at the stage that you should probably ask "your own question". This is someone elses question, and with a different topic to what you are now talking about. Please "Ask a question" if **you** want more of an answer to your own point. — Neil Lunn, Oct 03 '14 at 14:02

score 1 · Accepted Answer · answered Oct 06 '14 at 16:26

You don't have the right index for the query. The query planner selected the index on tags to fulfill the query, but that index doesn't help with the sort. Since you want to select based on tags and then sort on (start, end), try putting an index on { "tags" : 1, "start" : 1, "end" : 1 }. Having indexes on each separately isn't helpful here.

Sort limit reached for field which has an index

1 Answers1