Upsert is slow when using partial index and bulk operation

Working with Data
, ,
1

Test code:

const { MongoClient, BSONType } = require('mongodb');

const url = 'mongodb://localhost:27017';
const client = new MongoClient(url);

const dbName = 'datatest';

async function main() {
  await client.connect();
  const db = client.db(dbName);
  const collection = db.collection('documents');
  async function test() {
    console.time('operation')
    const bulk = collection.initializeUnorderedBulkOp();
    for (let i = 0; i < 5000; i++) {
      bulk.find({ id: i }).upsert().updateOne({
        $set: {
          id: i,
          test: Math.random().toString()
        }
      });
    }
    await bulk.execute();
    console.timeEnd('operation')
  }
  await collection.dropIndex("test");
  await collection.createIndex({ id: 1 }, {
    name: "test",
    unique: true,
    partialFilterExpression: {
      id: {
        $type: [BSONType.int, BSONType.long],
      }
    }
  })
  await test()
  await collection.dropIndex("test");
  await collection.createIndex({ id: 1 }, {
    name: "test",
    unique: true
  })
  await test()

}

main()

Log:

operation: 9.031s
operation: 738.926ms
2

Partial Indexes - Database Manual - MongoDB Docs.

This one works:

bulk.find({
    id: {
        $eq: i,
        $type: [BSONType.int, BSONType.long],
    },
}).upsert().updateOne({
    $set: {
        id: i,
        test: Math.random().toString(),
    },
});

$node a.js
operation: 1.882s
operation: 1.252s
2 Likes
3

To clarify why the original example does not use the partial index while the correct code in the response does, the issue is SERVER-20345 where we do not automatically figure out that the partial index applies in this case.

Asya

4

Another note - I would strongly suggest using $type: "number" rather than specific numeric subtypes when creating partial indexes, because in MQL all numeric types compare equal, and it can cause a problem if you only index one numeric subtype and inadvertently use a different numeric subtype.