MongoDB $in Performance: The Array Size Isn't the Problem

Search for advice on MongoDB $in performance and you get the same warning everywhere: never pass a large array of ObjectIds, it will wreck your query. Chunk it into batches. Watch out for the 16MB limit. The index will stop helping.

I measured it. A real mongod 8.2.6, half a million documents, $in arrays from 10 up to 100,000 ObjectIds. Almost none of that advice survived contact with the data.

The short version: $in never dropped the index, not even at 100,000 IDs. The array size barely mattered. And chunking — the fix everyone recommends — was slower in every run I did.

The setup

500,000 order documents, each with an indexed merchantId and the default _id index. Roughly 1,000 orders per merchant, which is a realistic marketplace shape. Everything below is reproducible — the script is at the end.

const merchants = Array.from({ length: 500 }, () => new ObjectId());

for (let i = 0; i < 500_000; i++) {
  batch.push({
    _id: new ObjectId(),
    merchantId: merchants[i % merchants.length],
    status: ["pending", "paid", "refunded", "failed"][i % 4],
    amount: Math.round(Math.random() * 100000),
    createdAt: new Date(Date.now() - Math.random() * 3.15e10),
  });
}

await col.createIndex({ merchantId: 1 });

Each query ran three times, and I took the fastest. Then I pulled explain("executionStats") to see what the planner actually did rather than guessing from the clock.

MongoDB $in performance on _id never degrades

Here is $in against the primary key, scaling the array from 10 to 100,000 ObjectIds:

  IDs        ms    keysExam  docsExam  returned  plan
  ───────────────────────────────────────────────────────────
       10      1.6        11        10        10  FETCH ← IXSCAN
      100      3.2       101       100       100  FETCH ← IXSCAN
     1000     15.8      1001      1000      1000  FETCH ← IXSCAN
     5000     43.0      5001      5000      5000  FETCH ← IXSCAN
    10000     87.4     10001     10000     10000  FETCH ← IXSCAN
    50000    458.2     50001     50000     50000  FETCH ← IXSCAN
   100000    890.5    100001    100000    100000  FETCH ← IXSCAN

Two things stand out. The plan stays FETCH ← IXSCAN at every size — there is no collection scan, no point where the planner gives up. And keysExamined is exactly the array length plus one, every single time.

That second number is the important one. Examining 100,001 keys to return 100,000 documents is perfect index efficiency. Not a single wasted read. The explain() output shows why:

{
  "stage": "FETCH",
  "inputStage": {
    "stage": "IXSCAN",
    "keyPattern": { "_id": 1 },
    "indexName": "_id_",
    "isUnique": true,
    "indexBounds": {
      "_id": [
        "[ObjectId('6a5746314aa373e379ecd723'), ObjectId('6a5746314aa373e379ecd723')]",
        "[ObjectId('6a5746314aa373e379ecd724'), ObjectId('6a5746314aa373e379ecd724')]"
      ]
    }
  }
}

MongoDB expands $in into one exact-match bound per value. Each bound is a point lookup on a B-tree. It is not scanning a range and filtering — it is doing N precise seeks. That is why the cost tracks the array length so cleanly, and why the index never stops helping.

Ten values and ten thousand, neck and neck

Now the same operator against merchantId, where each value matches about 1,000 documents:

  IDs        ms    keysExam  docsExam  returned  plan
  ───────────────────────────────────────────────────────────
       10     82.9     10001     10000     10000  FETCH ← IXSCAN
      100    845.3    100001    100000    100000  FETCH ← IXSCAN
      500   4175.6    500000    500000    500000  FETCH ← IXSCAN

Compare the first row to the _id table. Ten merchant IDs took 82.9ms. Ten thousand ObjectIds took 87.4ms. One array was a thousand times bigger than the other and they finished within 5% of each other. Which one edges ahead is run-to-run noise — I have runs going both ways.

Both returned 10,000 documents. That is the whole explanation. Divide time by documents returned across every row in this post and the number barely moves:

  _id        @  10,000     87.4 / 10,000  = 8.74 µs/doc
  _id        @ 100,000    890.5 / 100,000 = 8.91 µs/doc
  merchantId @      10     82.9 / 10,000  = 8.29 µs/doc
  merchantId @     100    845.3 / 100,000 = 8.45 µs/doc
  merchantId @     500   4175.6 / 500,000 = 8.35 µs/doc

About 8.5 microseconds per document, flat, whether the array held 10 values or 100,000. That constant is specific to this machine — a faster box gave me ~5µs — but it stays flat on every machine I have run it on. The array length does not appear in the cost. The document count is the cost.

The length of your $in array is close to irrelevant. What costs you is how many documents come back.

So "is 10,000 IDs too many?" is the wrong question. A 10,000-ID query returning 10,000 small documents is fine. A 5-ID query returning 500,000 documents will hurt. Ask what the query returns, not what you passed in.

Chunking makes it slower

The standard advice is to split a big $in into batches. I tested that with 10,000 IDs:

  single $in (10,000):           81.7 ms
  chunked @  500 (20 queries):  130.2 ms
  chunked @ 1000 (10 queries):  100.0 ms
  chunked @ 2500 ( 4 queries):   97.8 ms

Every chunk size lost, and smaller chunks lost harder — the 20-query version came in 59% slower than the single query. Across machines and repeated runs I have seen that penalty range from 20% to 80%, but I have never seen chunking win. The work is identical, the same 10,000 index seeks, so all chunking adds is 19 extra round trips and 19 more cursor setups.

And this ran on localhost, where a round trip is essentially free. In production every chunk pays a real one, so the penalty grows with your network. The recommended fix is worse than the problem on a laptop, and worse still on a hosted cluster.

The 16MB limit is a red herring

The other common warning is that a large $in will blow the 16MB BSON document limit. An ObjectId is 12 bytes, plus array overhead:

   10,000 ObjectIds ≈ 0.20 MB ✅
  100,000 ObjectIds ≈ 2.00 MB ✅
1,000,000 ObjectIds ≈ 20.00 MB ❌ over 16MB

You need roughly 800,000 ObjectIds before the query document itself becomes a problem. If you are passing 800,000 IDs to $in, the BSON ceiling is not your biggest design issue.

What to do instead

Since cost tracks documents returned, every real fix reduces that number rather than reshaping the array.

Project only the fields you need. Most of the 5µs per document is materializing and serializing it. Fetch less of each one:

// Returns whole documents — pays full cost per doc
const orders = await Order.find({ _id: { $in: ids } });

// Returns two fields — dramatically cheaper per doc
const orders = await Order.find({ _id: { $in: ids } })
  .select("status amount")
  .lean();

Paginate instead of pulling everything. If you need 100,000 documents in one request, the $in is not the bug — the requirement is. Page it.

Invert the query when the IDs came from another query. If you built the array by querying something else, you are doing two round trips to do one join:

// Two queries: fetch IDs, then feed them back in
const merchantIds = await Merchant.find({ region: "EU" }).distinct("_id");
const orders = await Order.find({ merchantId: { $in: merchantIds } });

// One query — let the server do the join
const orders = await Order.aggregate([
  { $lookup: {
      from: "merchants",
      localField: "merchantId",
      foreignField: "_id",
      as: "merchant",
  } },
  { $match: { "merchant.region": "EU" } },
]);

Do not chunk unless you are hitting a real limit. Chunking helps if you are near the BSON ceiling or need to bound memory on the client. It does not help throughput. The data above says it costs you.

What changes on Atlas

I ran this against a local mongod. Most people run MongoDB on Atlas or another hosted cluster, and I have not benchmarked those — I am not going to invent numbers for them. But the mechanism says which way each difference pushes, and none of it rescues a large $in returning a large result set.

Every query pays a round trip. Locally that is microseconds. On Atlas it is single-digit milliseconds from the same region, and far more across regions. My 1.6ms query for 10 documents becomes almost entirely network. Small queries stop being about the database at all.

Big result sets cross a wire. Locally, 100,000 documents move through shared memory. On Atlas they are batched over the network. The index work stays the same, but transfer is added on top — so documents returned costs you more on Atlas, not less. The whole argument of this post gets stronger, not weaker.

Shared tiers throttle. On M0, M2, and M5 the CPU is burstable and shared. Benchmarks there swing wildly and tell you about your neighbours as much as your query. If you are measuring, use a dedicated tier.

Working set versus RAM is the real cliff. My 500,000 documents fit in memory, so every seek hit a warm cache. If your index and hot documents do not fit in your tier's RAM, reads hit disk and page faults dominate everything measured here. That is a genuine cliff — and notice it is still not about $in. It is about how many documents you touch.

What does not change: the plan is still FETCH ← IXSCAN, and keysExamined is still exactly the array length plus one. Chunking still adds round trips, and hurts more on Atlas, where each one is real latency instead of a memcpy.

Caveats worth stating

These timings come from a local mongod 8.2.6 with a warm cache, temp-directory storage, and no network. Your production numbers will be slower and shaped by disk and latency. Treat the milliseconds as a relative comparison, not a forecast.

Every number here is the fastest of three attempts, and even so the same query varies 10-30% between runs on one machine, and up to 2x between machines. The tables above are a single run on one laptop. You will get different milliseconds.

That is worth being blunt about, because I nearly published a wrong claim from it. On a faster machine the 10-value query came out marginally slower than the 10,000-value one, and I wrote that gap up as a finding. On a slower machine it flipped. The gap was noise, and the honest statement is that the two are the same within measurement error — which is the actual point anyway.

So treat the milliseconds as relative. What does not move between runs or machines, and what the argument rests on:

• •The plan is FETCH ← IXSCAN at every size, every run, every machine.
• •keysExamined is exactly the array length plus one, every run, every machine.
• •Cost per document returned is flat across every array size — only the constant changes.
• •Chunking is slower than one query. I have never seen it win.

What does transfer is the explain() output. Index bounds, plan selection, and keysExamined are decisions made by the query planner, and they do not change because your disk is slower. The mechanism is the finding here. The clock is just the illustration.

One more limit: every document in this test was small. If yours are large, the per-document cost rises and reducing documents returned matters even more than it does here.

The takeaway

MongoDB expands $in into one index seek per value and executes it with no wasted reads. Passing 10,000 ObjectIds to an indexed field is a normal query, not an anti-pattern. If it feels slow, count the documents coming back before you touch the array.

Run it yourself and check my numbers. The script is on GitHub at usamaamjid/benchmarks. It needs no MongoDB install — mongodb-memory-server fetches a real pinned mongod 8.2.6 and tears it down after — and a run takes about a minute.

git clone https://github.com/usamaamjid/benchmarks
cd benchmarks
npm install
npm run mongo:in

If your numbers differ from mine, that is expected. If your explain() output differs, I want to know — open an issue.