A page and its total count: $facet against two queries, measured

Ask how to get one page of results and the total count in a single query, and this is the answer you will be given:

db.orders.aggregate([
  { $match: filter },
  { $sort: { createdAt: -1 } },
  { $facet: {
      data:  [{ $skip: 0 }, { $limit: 50 }],
      total: [{ $count: 'n' }]
  } }
])

One round trip instead of two, and one document back holding both answers. It is usually sold as the efficient way to paginate. The tutorials I read that recommend it never say what it costs the server, so I measured it against the two queries it replaces.

The page cost the same at every size, and the count grew with the number of matches, which is what you would expect. The $facet grew far faster than the count did. It reads every matching document in full to produce a number that the two-query version gets from the index alone.

What was measured

Two million orders, generated from a seed so the same command builds the same collection on any machine. Each one is the kind of order a list screen shows, with line items, a shipping address and a notes field, 875 bytes on average. 60% are shipped, which leaves 1,200,185 to page through. There is one index, { status: 1, createdAt: -1 }. It is the index this list needs, and both versions get it.

Each result size is a window over the newest shipped orders holding exactly that many: 1,000, 10,000, 100,000 and 1,000,000. Page 1 is therefore the same 50 documents at every size, and the only thing that changes is how much there is to count.

The two-query version is the page and the count as separate commands:

const page = await db.orders.find(filter).sort({ createdAt: -1 }).limit(50).toArray();
const total = await db.orders.countDocuments(filter);

Before timing anything, the runner checks that both versions return the same 50 ids and the same total. A fast wrong answer is not a result.

The result

Matching	Two queries	$facet
1,000	1.9 ms	2.2 ms
10,000	2.3 ms	14 ms
100,000	8.3 ms	191 ms
1,000,000	68 ms	1.80 s

At a thousand matches there is nothing between them. At ten thousand the $facet costs six times as much, and from a hundred thousand on it costs more than twenty times as much.

Split the two-query version into its halves and the cost is easy to place:

Matching	The page	The count
1,000	1.0 ms	0.8 ms
10,000	1.0 ms	1.4 ms
100,000	0.9 ms	7.2 ms
1,000,000	0.9 ms	67 ms

The page does not care how many documents match. The count grows with them, and at a million it is nearly the whole cost of the two-query version. So the $facet is not paying for pagination. It is paying for the count, and paying far more for it than the count costs on its own.

Where the time goes

The plan statistics show why, and they are the part worth checking against your own data. At 100,000 matches:

	Keys examined	Documents examined
The page	50	50
The count	100,000	0
$facet	100,000	100,000

The count walks 100,000 index keys and never opens a document. Every field in the filter is in the index, so each key in the window is one match, and counting keys is enough.

The $facet walks the same 100,000 keys and then fetches the 100,000 documents behind them. Its data branch returns whole documents, so what flows into the $facet has to be whole documents, and both branches read from that one stream. The count branch gets full orders it only needs to tally. The $limit that would let the page stop at 50 sits inside the $facet, and the index scan never sees it. It could not move below the $facet either, because the count needs the same stream to run to the last match.

So the server reads every matching order in full, keeps 50 of them and counts them all. On this machine the whole query came to about 1.8 microseconds per matching document. The 1.75 GB data set fit inside a 15.7 GB cache and every run was warmed first, so these are warm-cache figures.

The round trip, which is the real case for it

The tables above hide the one advantage $facet has. On localhost a round trip costs a fraction of a millisecond, so saving one is worth nothing. When the database sits in another region from the application, a round trip can cost tens of milliseconds, and that is the case $facet is for.

So here are the same runs again, through a proxy adding 10 ms in each direction:

Matching	Two queries, one after the other	Two queries, in parallel	$facet
1,000	48 ms	25 ms	27 ms
10,000	48 ms	25 ms	40 ms
100,000	55 ms	30 ms	207 ms
1,000,000	115 ms	90 ms	1.85 s

Against two queries awaited one after the other, the $facet wins on this link at 1,000 and 10,000 matches. That is the round trip it saves, and it is real.

The two queries do not depend on each other, though. Send them together and their round trips overlap:

const [page, total] = await Promise.all([
  db.orders.find(filter).sort({ createdAt: -1 }).limit(50).toArray(),
  db.orders.countDocuments(filter)
]);

In parallel they tied with the $facet at a thousand matches and beat it at every size after that. The round trip $facet saves is also available to the two-query version, for the price of a Promise.all.

What two commands do not give you is one pass. A $facet computes the page and the total from the same stream of documents, so the two agree with each other. Two commands can see different data if a write lands between them, and the total is then off by whatever that write changed. On a list screen that rarely matters. Where the total has to match the page exactly, it is a real reason to keep the $facet.

Moving the sort into the $facet

A common variant puts the $sort inside the data branch instead of before the $facet. Measured, it cost about a tenth more: 209 ms against 191 ms at 100,000 matches, 1.99 s against 1.80 s at a million. The sort cannot use the index from in there, but it added only a tenth, since both versions read all of the matching documents either way.

A deep page

Page 201 is skip: 10000. At 100,000 matches:

	Page 1	Page 201
The page alone	0.9 ms	2.6 ms
$facet	191 ms	180 ms

The page got slower, and the plan shows how much work that took. It walked 10,050 index keys to skip 10,000, but it fetched only the 50 documents it returned, because the skip ran on index keys before the fetch. The $facet stayed where it was, because it had read every match for page 1 as well.

The usual way past skip on deep pages is a range query on the sort key, with _id added to the sort and to the index so that equal timestamps keep one order. This post did not measure it.

The one document has limits of its own

A $facet returns its page and its count inside one document, and a document the server returns is capped at 16 MiB. A page of 50 orders is nowhere near that. A page of 20,000 is. Asked for 20,000 of these orders, find returned all of them, 16.7 MiB in total, because a cursor hands results back in batches and never builds them into one document. The same request through $facet failed with error code 10334, BSONObjectTooLarge:

Executor error during aggregate command on namespace: facet_bench.orders
:: caused by :: Serializing Document failed
:: caused by :: Size 17651390 exceeds maximum 16793600

Nobody shows 20,000 rows on a screen, but the same recipe gets reused for exports and “download all”, and that is where it breaks. The post on BSONObjectTooLarge covers finding what fills a document.

MongoDB’s own $facet reference adds a second limit. It says the document that each stage in a $facet produces is limited to 100 MB, and that allowDiskUse does not change that, because $facet cannot spill to disk. Since MongoDB 6.0 the stages that can spill do so by default, as the allowDiskUse post explains, so this is a place where the old 100 MB wall still stands.

What to write instead

Send the two queries together. Over the slow link that cost about the same wait as the $facet’s single round trip, and the server reads 50 documents rather than every match.

Cap the count once the number gets large. At a million matches the count is nearly all of the cost, and a reader does not act differently on “1,000,000 results” than on “1,000+ results”. countDocuments takes a limit:

const total = await db.orders.countDocuments(filter, { limit: 1001 });
// 1001 means "more than 1,000"

That stops after at most 1,001 index keys. On the direct connection it took under a millisecond at every size measured, a million included.

Or do not count at all. Ask for 51 documents and show 50. If the 51st exists there is a next page, and that is all a Next button needs to know.

When $facet is the right tool

When you want several answers about the same set of documents. A search page with counts by category, by price band and by brand beside the results is the job $facet is named after. Each of those branches needs every match anyway, so reading them all once is the work you asked for rather than overhead.

For a page and a count, Promise.all saves the same round trip. What $facet still offers there is a total that agrees with its page, and the price of that is reading every match.

What this does not say

This is one machine, one container and one MongoDB version, 8.3.8, on a single node. The whole data set fit in the cache and every run was warmed first, so these are warm-cache figures. On a cold cache the $facet fetch reads documents from disk while the count reads index pages, and this did not measure what that does to the gap.

The added 20 ms comes from a proxy on the same machine. It is a steady delay, without the jitter a real network adds.

The milliseconds will not transfer to your cluster, and your plan may not match this one: a filter the index does not cover makes the count fetch documents too. What transfers is the check. Run explain('executionStats') on your own $facet and compare totalDocsExamined with the size of your page. If it matches the number of results instead, this post is about your query.

Reproducing it

git clone https://github.com/Kontsedal/monghoul-public
cd monghoul-public/benchmarks/facet-pagination
npm install
docker run -d -p 27017:27017 mongo:8.3.8
node generate.mjs --uri mongodb://localhost:27017 --docs 2000000
node run.mjs --uri mongodb://localhost:27017

run.mjs prints the tables above for your hardware, with the keys and documents examined beside every timing. --skip 10000 times page 201 and --size-check runs the 16 MB test. The README shows how to put Toxiproxy in front of the server for the round-trip figures.