Details
Description
$in queries are performing an unnecessary/inefficient SORT stage compared to $or using a SORT_MERGE for a logically equivalent query in mongos.
With $in examines 2000 keys.
db.items.find(
|
{item: "a", i_type: {$in: ["x", "y"]}},
|
{_id: 0, item: 1, i_type: 1, price: 1}
|
).sort({price: 1}).limit(10).explain("executionStats")
|
With $or examines 11 keys.
db.items.find(
|
{$or: [{item: "a", i_type: "x"}, {item: "a", i_type: "y"}]},
|
{_id: 0, item: 1, i_type: 1, price: 1}
|
).sort({price: 1}).limit(10).explain("executionStats")
|
Additional queries I've tested:
Without sort examines 10 keys.
db.items.find(
|
{item: "a", i_type: {$in: ["x", "y"]}},
|
{_id: 0, item: 1, i_type: 1, price: 1}
|
).limit(10).explain("executionStats")
|
With only one i_type examines 10 keys.
db.items.find(
|
{item: "a", i_type: {$in: ["x"]}},
|
{_id: 0, item: 1, i_type: 1, price: 1}
|
).sort({price: 1}).limit(10).explain("executionStats")
|
Run directly on replica set examines 11 keys.
db.items.find(
|
{item: "a", i_type: {$in: ["x", "y"]}},
|
{_id: 0, item: 1, i_type: 1, price: 1}
|
).sort({price: 1}).limit(10).explain("executionStats")
|