Investigating totalKeysExamined

In the last two entries, we have seen how the executionStats improved overtime. In the first version, COLLSCAN stage scanned 6 documents (totalDocsExamined) to return 2 documents, and in the second version, IXSCAN was the stage and only 2 documents were scanned (totalDocsExamined). However, the total keys examined is 4(totalKeysExamined), which makes us ask why does it need to scan 4 index keys if it only needs to return 2 documents.

db.orders
	.find({ status: "placed", total_price: { $gte: 500, $lte: 1000 } })
	.explain("executionStats");

Just a recap, these are the inserted documents

db.orders.insertMany([
	{
		status: "placed",
		total_price: 100,
	},
	{
		status: "placed",
		total_price: 500,
	},
	{
		status: "placed",
		total_price: 1000,
	},
	{
		status: "complete",
		total_price: 200,
	},
	{
		status: "complete",
		total_price: 600,
	},
	{
		status: "complete",
		total_price: 1200,
	},
]);

Let’s check the index keys when we query all documents. Remember that previously, we created this index: total_price_1_status_1.

db.orders.find().hint("total_price_1_status_1").returnKey();

[
	({
		_id: ObjectId("69e229b79cd866c677544ca7"),
		status: "placed",
		total_price: 100,
	},
	{
		_id: ObjectId("69e229b79cd866c677544caa"),
		status: "complete",
		total_price: 200,
	},
	{
		_id: ObjectId("69e229b79cd866c677544ca8"),
		status: "placed",
		total_price: 500,
	},
	{
		_id: ObjectId("69e229b79cd866c677544cab"),
		status: "complete",
		total_price: 600,
	},
	{
		_id: ObjectId("69e229b79cd866c677544ca9"),
		status: "placed",
		total_price: 1000,
	},
	{
		_id: ObjectId("69e229b79cd866c677544cac"),
		status: "complete",
		total_price: 1200,
	}),
];

To easily visualize index keys, the result above is transformed to the array below. If we try to traverse the index keys given the query ({ status: "placed", total_price: { $gte: 500, $lte: 1000 } }):

1. The price is >= 500 and <= 1000, the leftmost part of the key tells us that we start at 3rd and end at 5th row.
2. Now we have to check the status individually, because not all status is placed.
3. So we start counting at 3rd row (placed), skip 4th row (complete), count 5th row and end at 6th row (stopped as the total_price is 1200).
4. In total, we visited 4 keys. This is why totalKeysExamined is 4.
5. Regarding point # 3, why is there an extra step? This is because although we have the price range, MongoDB cannot perform a range on the second field key. Notice that the values change from complete to placed.

[
	{ total_price: 100, status: "placed" },
	{ total_price: 200, status: "complete" },
	{ total_price: 500, status: "placed" },
	{ total_price: 600, status: "complete" },
	{ total_price: 1000, status: "placed" },
	{ total_price: 1200, status: "complete" },
];