Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-27150

Performance degradation with $sort on indexed field

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.3.12, 3.4.0-rc3
    • Component/s: Aggregation Framework
    • None
    • Environment:
      Windows 10, Ubuntu Linux
    • Query
    • ALL
    • Hide 
      var c = db.c;
c.drop();

c.ensureIndex( { a: 1 } );

//insert 100 000 documents
for (var thousands=0; thousands < 100; thousands++) {
	for (var i=0; i < 1000; i++) {
		var bulk = c.initializeUnorderedBulkOp();
		bulk.insert({ a: i, b: new Date()} );
		bulk.execute();
	}
}

//takes 2ms to execute
c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]);

//create index on "b" field
c.ensureIndex( { b: 1 } );

//now the same query takes more than 90ms
c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]);

//let's add a composite index on both fields
c.ensureIndex( { a: 1, b: 1 } );

//aggregation with composite index takes 2ms
c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]);

      Query plan of slow query:

      { 
    "stages" : [
        {
            "$cursor" : {
                "query" : {
                    "a" : NumberInt(1)
                }, 
                "sort" : {
                    "b" : NumberInt(-1)
                }, 
                "queryPlanner" : {
                    "plannerVersion" : NumberInt(1), 
                    "namespace" : "PanelRider.c", 
                    "indexFilterSet" : false, 
                    "parsedQuery" : {
                        "a" : {
                            "$eq" : NumberInt(1)
                        }
                    }, 
                    "winningPlan" : {
                        "stage" : "FETCH", 
                        "filter" : {
                            "a" : {
                                "$eq" : NumberInt(1)
                            }
                        }, 
                        "inputStage" : {
                            "stage" : "IXSCAN", 
                            "keyPattern" : {
                                "b" : 1.0
                            }, 
                            "indexName" : "b_1", 
                            "isMultiKey" : false, 
                            "isUnique" : false, 
                            "isSparse" : false, 
                            "isPartial" : false, 
                            "indexVersion" : NumberInt(1), 
                            "direction" : "backward", 
                            "indexBounds" : {
                                "b" : [
                                    "[MaxKey, MinKey]"
                                ]
                            }
                        }
                    }, 
                    "rejectedPlans" : [

                    ]
                }
            }
        }
    ], 
    "ok" : 1.0
}
      Show
      var c = db.c; c.drop(); c.ensureIndex( { a: 1 } ); //insert 100 000 documents for ( var thousands=0; thousands < 100; thousands++) { for ( var i=0; i < 1000; i++) { var bulk = c.initializeUnorderedBulkOp(); bulk.insert({ a: i, b: new Date()} ); bulk.execute(); } } //takes 2ms to execute c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]); //create index on "b" field c.ensureIndex( { b: 1 } ); //now the same query takes more than 90ms c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]); //let's add a composite index on both fields c.ensureIndex( { a: 1, b: 1 } ); //aggregation with composite index takes 2ms c.aggregate([ { $match: { a: 1 } }, { $sort: { b : 1 } } ]); Query plan of slow query: { "stages" : [ { "$cursor" : { "query" : { "a" : NumberInt(1) }, "sort" : { "b" : NumberInt(-1) }, "queryPlanner" : { "plannerVersion" : NumberInt(1), "namespace" : "PanelRider.c" , "indexFilterSet" : false , "parsedQuery" : { "a" : { "$eq" : NumberInt(1) } }, "winningPlan" : { "stage" : "FETCH" , "filter" : { "a" : { "$eq" : NumberInt(1) } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "b" : 1.0 }, "indexName" : "b_1" , "isMultiKey" : false , "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : NumberInt(1), "direction" : "backward" , "indexBounds" : { "b" : [ "[MaxKey, MinKey]" ] } } }, "rejectedPlans" : [ ] } } } ], "ok" : 1.0 }

      The $sort operator on indexed field slows down aggregation pipeline execution up to 200 times when used in conjunction with $match on another indexed field.

      Performance degrades linearly on collection growth. It becomes a huge problem after 1 000 000 docs. Aggregations on my production environment with 9 000 000 docs collection take more than 4s.

      It seems that problem itself is related to the non-optimal index usage.

      I found only two options:

      • Remove the index on the sorted field (unacceptable in my case).
      • Add the compound index on both fields (not the best decision in case of multiple queries on various fields with different sort order).

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            kreig Vyacheslav Stroy
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: