index:
{
A: 1,
B: 2
}
{
A: 1,
C: 2
}
Query:
user.aggregate([{
$match: {
A: "house",
$or: [
{ B: "car" },
{ C: "boat" },
]
}
}
])
In order to check the $or condition, will mongodb make use of both indexes?
EDITED:
According to the documentation on mongo’s site, multiple single field can indexes can be used in the same query. However, in my question, both indexes are compound indexes.
Hey @Big_Cat_Public_Safety_Act,
According to the documentation on mongo’s site, multiple single field can indexes can be used in the same query. However, in my question, both indexes are compound indexes.
If you evaluate the Explain Results for your query you’ll see that a plan containing an OR stage (which evaluates more than 1 plan/index) will be generated:
db.foo.drop();
db.foo.insertOne([{}]);
db.foo.createIndexes([{ A: 1, B: 1 }, { A: 1, C: 1 }]);
db.foo.explain("executionStats").aggregate([
{ $match: {
A: "house",
$or: [
{ B: "car" },
{ C: "boat" }
]
}}
]);
{
"explainVersion": "2",
"queryPlanner": {
"namespace": "test.foo",
"indexFilterSet": false,
"parsedQuery": {
[...]
},
[...]
"winningPlan": {
"queryPlan": {
"stage": "FETCH",
[...]
"inputStage": {
"stage": "IXSCAN",
"planNodeId": 1,
"keyPattern": {
"A": 1,
"B": 1
},
"indexName": "A_1_B_1",
[...]
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s12 env: { s4 = 1692189851389 (NOW), s7 = KS(3C686F75736500F0FE04), s1 = TimeZoneDatabase(Atlantic/Canary...America/Hermosillo) (timeZoneDB), s17 = \"boat\", s11 = {\"A\" : 1, \"B\" : 1}, s2 = Nothing (SEARCH_META), s3 = Timestamp(1692189850, 7) (CLUSTER_TIME), s6 = KS(3C686F757365000A0104), s16 = \"car\" }",
"stages": "[2] filter {(traverseF(s14, lambda(l1.0) { ((l1.0 == s16) ?: false) }, false) || traverseF(s15, lambda(l2.0) { ((l2.0 == s17) ?: false) }, false))} \n[2] nlj inner [] [s5, s8, s9, s10, s11] \n left \n [1] cfilter {(exists(s6) && exists(s7))} \n [1] ixseek s6 s7 s10 s5 s8 s9 [] @\"011d24a7-fd1c-442a-9a28-101bcb8732b0\" @\"A_1_B_1\" true \n right \n [2] limit 1 \n [2] seek s5 s12 s13 s8 s9 s10 s11 [s14 = B, s15 = C] @\"011d24a7-fd1c-442a-9a28-101bcb8732b0\" true false \n"
}
},
"rejectedPlans": [
{
"queryPlan": {
"stage": "FETCH",
"planNodeId": 4,
"inputStage": {
"stage": "OR",
"planNodeId": 3,
"inputStages": [
{
"stage": "IXSCAN",
"planNodeId": 1,
"keyPattern": {
"A": 1,
"B": 1
},
"indexName": "A_1_B_1",
"isMultiKey": false,
"multiKeyPaths": {
"A": [],
"B": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"A": [
"[\"house\", \"house\"]"
],
"B": [
"[\"car\", \"car\"]"
]
}
},
{
"stage": "IXSCAN",
"planNodeId": 2,
"keyPattern": {
"A": 1,
"C": 1
},
"indexName": "A_1_C_1",
"isMultiKey": false,
"multiKeyPaths": {
"A": [],
"C": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"A": [
"[\"house\", \"house\"]"
],
"C": [
"[\"boat\", \"boat\"]"
]
}
}
]
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s24 env: { s4 = 1692189851389 (NOW), s7 = KS(3C686F757365003C63617200FE04), s1 = TimeZoneDatabase(Atlantic/Canary...America/Hermosillo) (timeZoneDB), s14 = KS(3C686F757365003C626F617400FE04), s11 = {\"A\" : 1, \"B\" : 1}, s2 = Nothing (SEARCH_META), s18 = {\"A\" : 1, \"C\" : 1}, s3 = Timestamp(1692189850, 7) (CLUSTER_TIME), s6 = KS(3C686F757365003C636172000104), s13 = KS(3C686F757365003C626F6174000104) }",
"stages": "[4] nlj inner [] [s19, s21, s23, s20, s22] \n left \n [3] unique [s19] \n [3] union [s23, s20, s22, s19, s21] \n branch0 [s9, s10, s11, s5, s8] \n [1] cfilter {(exists(s6) && exists(s7))} \n [1] ixseek s6 s7 s10 s5 s8 s9 [] @\"011d24a7-fd1c-442a-9a28-101bcb8732b0\" @\"A_1_B_1\" true \n branch1 [s16, s17, s18, s12, s15] \n [2] cfilter {(exists(s13) && exists(s14))} \n [2] ixseek s13 s14 s17 s12 s15 s16 [] @\"011d24a7-fd1c-442a-9a28-101bcb8732b0\" @\"A_1_C_1\" true \n right \n [4] limit 1 \n [4] seek s19 s24 s25 s21 s23 s20 s22 [] @\"011d24a7-fd1c-442a-9a28-101bcb8732b0\" true false \n"
}
},
[...]
In the case of my example above, though multiple plans were generated (one containing an OR stage), the winningPlan in this case was a single index plan.
This is due to the query planner evaluating that index as performing best during the trial phase (which makes sense given the amount of data in in our collection at the moment).
If you explain your operation you should be able to see if multiple indexes are being used in the winningPlan if there’s an OR stage present.
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.