Loading...

Type: Improvement
Resolution: Works as Designed
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 3.6.8
Component/s: Aggregation Framework, Querying
Labels:
None

Case:
Confidence Status:
None
Work Order:
3

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name:
None
Goal Link:
None

Hi,

I've a performance issue with a collection with almost 4M documents, because the find (or $match on aggregation) is executing a FETCH when I use the $elemmatch. Even if the index is working well and returning only the correct documents.
The FETCH is not necessary because on version 3.6 the multiKeyPaths is working and makes the $elemmatch query to filter well on date ranges. So I think MongoDB should avoid executing the FETCH since it causes a huge performance drop.

here is an example of the query planner:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mp.userdocuments",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "userActs" : {
                        "$elemMatch" : {
                            "publishedDate2" : {
                                "$elemMatch" : {
                                    "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                    "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                                }
                            }
                        }
                    }
                }, 
                {
                    "isArchived" : {
                        "$eq" : false
                    }
                }, 
                {
                    "user" : {
                        "$eq" : ObjectId("556efbaaef2541ec0d80a1ba")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "userActs" : {
                    "$elemMatch" : {
                        "publishedDate2" : {
                            "$elemMatch" : {
                                "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                            }
                        }
                    }
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "user" : 1,
                    "isArchived" : 1,
                    "userActs.publishedDate2" : 1
                },
                "indexName" : "user_1_isArchived_1_userActs.publishedDate2_1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "user" : [],
                    "isArchived" : [],
                    "userActs.publishedDate2" : [ 
                        "userActs", 
                        "userActs.publishedDate2"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "user" : [ 
                        "[ObjectId('556efbaaef2541ec0d80a1ba'), ObjectId('556efbaaef2541ec0d80a1ba')]"
                    ],
                    "isArchived" : [ 
                        "[false, false]"
                    ],
                    "userActs.publishedDate2" : [ 
                        "[new Date(1483295508000), new Date(1538418708000)]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 13033,
        "executionTimeMillis" : 175,
        "totalKeysExamined" : 15228,
        "totalDocsExamined" : 13033,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "userActs" : {
                    "$elemMatch" : {
                        "publishedDate2" : {
                            "$elemMatch" : {
                                "$lte" : ISODate("2018-10-01T18:31:48.000Z"),
                                "$gte" : ISODate("2017-01-01T18:31:48.000Z")
                            }
                        }
                    }
                }
            },
            "nReturned" : 13033,
            "executionTimeMillisEstimate" : 152,
            "works" : 15229,
            "advanced" : 13033,
            "needTime" : 2195,
            "needYield" : 0,
            "saveState" : 119,
            "restoreState" : 119,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 13033,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 13033,
                "executionTimeMillisEstimate" : 41,
                "works" : 15229,
                "advanced" : 13033,
                "needTime" : 2195,
                "needYield" : 0,
                "saveState" : 119,
                "restoreState" : 119,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "user" : 1,
                    "isArchived" : 1,
                    "userActs.publishedDate2" : 1
                },
                "indexName" : "user_1_isArchived_1_userActs.publishedDate2_1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "user" : [],
                    "isArchived" : [],
                    "userActs.publishedDate2" : [ 
                        "userActs", 
                        "userActs.publishedDate2"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "user" : [ 
                        "[ObjectId('556efbaaef2541ec0d80a1ba'), ObjectId('556efbaaef2541ec0d80a1ba')]"
                    ],
                    "isArchived" : [ 
                        "[false, false]"
                    ],
                    "userActs.publishedDate2" : [ 
                        "[new Date(1483295508000), new Date(1538418708000)]"
                    ]
                },
                "keysExamined" : 15228,
                "seeks" : 1,
                "dupsTested" : 15228,
                "dupsDropped" : 2195,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "ML-Lenovo",
        "port" : 27017,
        "version" : "3.6.8",
        "gitVersion" : "6bc9ed599c3fa164703346a22bad17e33fa913e4"
    },
    "ok" : 1
}

So, as you can see, from 23K documents (for this user id), the filter correctly returns 13033 rows, and then the FETCH is executed and also returns 13033, because it is doing the same filter again, so the result will not be different.

Here is an example of a document:

{
    "_id" : ObjectId("5b4d188ca958130c528f21ac"),
    "user" : ObjectId("556efbaaef2541ec0d80a1ba"),
    "isArchived" : false,
    "userActs" : [ 
        {
            "publishedDate2" : [ 
                ISODate("2016-02-15T05:00:00.000Z")
            ]
        }, 
        {
            "publishedDate2" : [ 
                ISODate("2016-03-07T05:00:00.000Z")
            ]
        }, 
        {
            "publishedDate2" : [ 
                ISODate("2016-10-04T04:00:00.000Z")
            ]
        }, 
        {
            "publishedDate2" : [ 
                ISODate("2016-10-13T04:00:00.000Z")
            ]
        }, 
        {
            "publishedDate2" : [ 
                ISODate("2017-01-04T05:00:00.000Z")
            ]
        }, 
        {
            "publishedDate2" : [ 
                ISODate("2018-03-05T05:00:00.000Z")
            ]
        }
    ],
    "__v" : 6
}

The index is:

{
    "user" : 1,
    "isArchived" : 1,
    "userActs.publishedDate2" : 1
}

The execution of the FETCH stage is killing the performance.
Can we fix this and avoiding the FETCH stage when the index isMultiKey and the fields on $elemMatch are covered by the index and are in the multiKeyPaths?

Otherwise, I guess, I'll need to change my structure to not use array? (not good since we would need to duplicate a lot of data).

Thanks

related to

SERVER-35223 $elemMatch forces mongo to fetch documents instead of using COUNT_SCAN

Closed

Details

Description

Attachments

Issue Links

Activity

People

Dates