CODEWITHSUNDEEP
javamongodbsortingmongo-java
Pratik Patel
Pratik Patel

Reputation: 1345

Sort limit reached for field which has an index

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?

Upvotes: 0

Views: 493

Answers (1)

wdberkeley
wdberkeley

Reputation: 11671

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.

Upvotes: 1

Related Questions