BUG: Node driver does not use indexes created with MongoDB compass (proof)!

Working with Data Drivers & ODMs
, , , ,
#1

Summary: Indexes created inside mongoDB compass are not used by Node driver. Using serverless.

Explanation/proof:

Running the following command in MongoDB compass gets me the following result:

db.getCollection('users').getIndexKeys()

[
  { _id: 1 },
  { email: 1 },
  { oauthId: 1 },
  { _fts: 'text', _ftsx: 1 },
  { amplify_id: 1 }
]

I filter on the amplify_id field for each operation, which looks just like this:

mongodb.collection('users').find(
            { amplify_id: "123" },
        )

And when I use the “explain” keyword on a “find” operation in my NODE script, the result is this:

await mongodb.collection('users').find(
            { amplify_id },
        ).explain()

totalDocsExamined: 1964,

However, when I do this in MongoDB Compass, I get 1 doc examined, as expected. Same query.

Node recognizes that an index exists:

await mongodb.collection<MongoUser>('users').indexes();

{
    v: 2,
    key: { amplify_id: 1 },
    name: 'amplify_id_1',
    unique: true,
    sparse: true
  }

…but does not use it.

This index was created in MongoDB compass. And sure enough, when I delete the index, then re-create it inside Node or with the mongo shell, it works:

totalDocsExamined: 1,

This is a huge problem and and can easily lead to unexpectedly high RPUs (billing going through the roof) and slow performance, and is not straightforward to detect.

I pointed out another bug with the Node driver not using TEXT indexes. The workaround was the same; create the index IN NODE, not in MongoDB Compass.

I’ll add that I really enjoy working in the Compass and it has been just fine for doing operations on data, but I won’t be using it anymore for deeper operations like creating indexes

WARNING! -- Mongo DB Serverless Pricing
#3

Hi @Justin_Jaeger,

Thanks for providing those details.

I’m going to try reproduce this but this is my understanding of the general steps needed to reproduce this behaviour:

(Note: The tests will be performed against a serverless instance)

  1. Insert several test documents:
{"amplify_id" : 123},
{"amplify_id" : 124},
{"amplify_id" : 125},
{"amplify_id" : 126},
{"amplify_id" : 127}
  1. Create an index {"amplify_id": 1} via mongosh (in this case)
  2. Verify the indexes using db.collection.getIndexes()
  3. Run a query that utilises the index, get the explain("executionStats") output:
db.collection.find({"amplify_id": 123}).explain("executionStats")
  1. Perform steps 1 - 4 again but at step 2, use MongoDB Compass to create the index
  2. Compare execution stats for both queries run (particularly with docs examined).

With the above steps, I am comparing the same index but created one created via mongosh and another via MongoDB compass.

If you believe something is missing or have any extra information that may help this, please let me know.

Look forward to hearing from you.

Best Regards,
Jason

#4

Hi Jason,

Thanks for attending to this!

These steps are correct, but at #6, the key is to compare the results of the query inside of the Node driver AND MongoDB compass). These should produce different execution stats.

After creating the index in MongoDB Compass, I’d expect when you query in there, it will use the index. Whereas when you query in Node it won’t use the index.

#5

I’d also like to point out one more bug I’m observing which is similar. As stated, I fixed the indexing problem above by creating all my indexes in the mongo shell instead of compass. Except one. My text index was still not being recognized. That is, until I deleted it, and re-created it in Node.

Reproduce:

  1. create index in Mongodb shell via mongosh
mongodb.collection('users').createIndex({ name: 'text' })
  1. explain text search query in node. expect it to not recognize any text indexes (error: “need exactly one text index for $text query”)
mongodb.collection('users').find({$text:{$search:'hello'}}).explain()
  1. Delete the index, re-create it in Node by turning off strict mode.
  2. Run the query again from #2, observe it using the index
#6

@Justin_Jaeger this is a very strange scenario you’ve described as mongosh, as well as MongoDB Compass all use the Node.js driver to communicate with your cluster.

To help us better understand the issue you’ve described can you share:

  1. The version of the Node.js driver being used
  2. The version of Compass being used
  3. The version of mongosh being used

This will help us ensure our reproductions match your environment as closely as possible.

#7

For the moment I’ve tried this using MongoDB Compass 1.39.3, mongosh 1.10.6 and Node.js driver v6.0.0 and performed the following:

  1. Connect to serverless instance via Compass
  2. Create new collection
  3. Insert 5 documents
  4. Create index
  5. Connect to serverless cluster via mongosh
  6. Verify index exists
  7. Try filtering for 1 document and generating an explain plan to verify the index is used (it is)
  8. Create a Node.js script to validate the same operation uses the index (it does)

At the moment everything appear to be working as expected. If it helps the script I used in Node is:

const { MongoClient } = require("mongodb");
const client = new MongoClient("mongodb+srv://xyz.mongodb.net/");
async function run() {
  try {
    await client.connect();
    const database = client.db("test");
    const coll = database.collection("amplify");    
    var result = await coll.find({ amplify_id: 1 }).explain();
    console.log(result);
  } finally {
    await client.close();
  }
  process.exit(0);
}
run().catch(console.dir);
3 Likes