[SERVER-60808] mongodb3.4 upgrade 4.2, query plan isMultiKey attributes change Created: 19/Oct/21  Updated: 27/Oct/23  Resolved: 04/Nov/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: 杰城 施 Assignee: Edwin Zhou
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2021-10-19-18-40-42-368.png    
Operating System: ALL
Participants:

 Description   

table like this: 

insert command:
db.getCollection("teachers").insert(

{ _id: ObjectId("6124cd389c7200001a000cb9"), name: "jack", students: [ 1, 2, 3 ] }

);
db.getCollection("teachers").insert(

{ _id: ObjectId("616e9d3aad1100002b002009"), name: "marray", students: [ ] }

);

*In mongodb 4.2*
using index:
db.teachers.createIndex(

{ "students.0": 1 }

,

{ name: "IX_first_student", }

);

query:
db.teachers.find({"students.0": 1}).explain();

result:
{
plannerVersion: NumberInt("1"),
namespace: "myscrm_kktyadmin.teachers",
indexFilterSet: false,
parsedQuery: {
"students.0":

{ $eq: 1 }

},
queryHash: "6ECDFB50",
planCacheKey: "88E51EB0",
winningPlan: {
stage: "FETCH",
inputStage: {
stage: "IXSCAN",
keyPattern:

{ "students.0": 1 }

,
indexName: "IX_first_student",
isMultiKey: true,
multiKeyPaths:

{ "students.0": [ "students" ] }

,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: NumberInt("2"),
direction: "forward",
indexBounds:

{ "students.0": [ "[1.0, 1.0]" ] }

}
},
rejectedPlans: [ ]
}

*mongodb3.4*
using index:
db.teachers.createIndex(

{ "students.0": 1 }

,

{ name: "IX_first_student", }

);

query:
db.teachers.find({"students.0": 1}).explain();

result:
{
plannerVersion: NumberInt("1"),
namespace: "myscrm_kktyadmin.teachers",
indexFilterSet: false,
parsedQuery: {
"students.0":

{ $eq: 1 }

},
winningPlan: {
stage: "FETCH",
inputStage: {
stage: "IXSCAN",
keyPattern:

{ "students.0": 1 }

,
indexName: "IX_first_student",
isMultiKey: false,
multiKeyPaths:

{ "students.0": [ ] }

,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: NumberInt("2"),
direction: "forward",
indexBounds:

{ "students.0": [ "[1.0, 1.0]" ] }

}
},
rejectedPlans: [ ]
}

question is why in mongodb 4.2 isMultiKey is true?

in mongodb 4.2, this behavior make some query can't hint index, plz help.



 Comments   
Comment by Edwin Zhou [ 04/Nov/21 ]

Hi jcs12311@gmail.com,

Thanks for following up that creating an index with partialFilterExpression resolved this issue after upgrading from 3.4 to 4.2. A number of changes were made to indexing since MongoDB v3.2 and it's possible one of those changes may have affected what you see in the explain plan. If you're interested, you may be able to find some of the changes made here

Since you've been able to work around this problem, I will now close this ticket.

Best,
Edwin

Comment by 杰城 施 [ 26/Oct/21 ]

i using partialFilterExpression to createIndex, fix this problem

Generated at Thu Feb 08 05:50:47 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.