Search code examples
node.jsmongodbmongoose

Bad performance on a sorting request

I have a very simple query in a NodeJS / Mongoose application:

const blocks = await Block
  .find({
    content: ObjectId(internalId),
  })
  .sort({ position: 1, _id: 1 })

with the schema:

const BlockSchema = mongoose.Schema({
  id: String,
  (...)
  content: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Domain',
    index: true
  },
  concept: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'ConceptDetails',
    index: true
  },
  conceptDetails: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'ConceptDetails',
    index: true
  },
  creator: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  }
});


const Block = mongoose.model(
  'Block',
  BlockSchema
);

The performance of this simple query was really bad with real data (around 900ms) so I added the following index:

db.blocks.createIndex({ position: 1, _id: 1 });

It improves the performance (around 330ms) but I expected to have something better for a request like that. FYI I have 13100 block items in the database.

Is there something else I can do to improve performance? Thanks for your help!

Solution

  • It is because you have a find clause to filter by content. This makes the index not usable. You can check this with explain(). Below is a visualization of the query plan on my local replication of your scenario. You can see COLLSCAN, which indicates the index is not used. enter image description here

    What can we do?

    We can build another compound index, which includes the field content to speed up the query. Make sure content is before the your sort fields position and _id so the index can be utilized

    db.collection.createIndex({content: 1, position: 1, _id: 1 })

    You can check again the query plan: enter image description here You can see the query plan changed to IXSCAN, which utilized the new index. You can then expect a faster query benefit from index scan.

    You can check out this official doc for more details on query coverage and optimization.