Hi,
I have a problem with the functioning of the application related to the long response time to the find query.
This is quite strange because if I run the query myself (e.g. via the dataGrip / Intellij driver) the response time is about 500-700 ms, and when it is performed by an application that uses spring data mongo, the response time is from 33000 ms to 45000 ms. Not only the time is different - but verifying the query more specifically using.explain(“executionStats”) - the number of keysExamined.
This is the query:
db.fooCollection.find(
{"$or": [{"$and": [{"params": {"$elemMatch": {"name": "manufacturer", "val": "FooManufacturer"}}}, {"params": {"$elemMatch": {"name": "articleId", "val": "FOO-FOO-ARTICLE-ID-FOO"}}}]}, {"$and": [{"params": {"$elemMatch": {"name": "brand", "val": "FooManufacturer"}}}, {"params": {"$elemMatch": {"name": "articleId", "val": "FOO-FOO-ARTICLE-ID-FOO"}}}]}]}
).collation({locale: 'en', strength: 1, alternate: "shifted", maxVariable: "punct"})
.explain("executionStats")
This is executionStats from manual query run:
{"executionSuccess": true, "nReturned": new NumberInt("1"), "executionTimeMillis": new NumberInt("2"), "totalKeysExamined": new NumberInt("1"), "totalDocsExamined": new NumberInt("1"), "executionStages": {"stage": "SUBPLAN", "nReturned": new NumberInt("1"), "executionTimeMillisEstimate": new NumberInt("2"), "works": new NumberInt("2"), "advanced": new NumberInt("1"), "needTime": new NumberInt("0"), "needYield": new NumberInt("0"), "saveState": new NumberInt("0"), "restoreState": new NumberInt("0"), "isEOF": new NumberInt("1"), "inputStage": {"stage": "FETCH", "filter": {"$or": [{"$and": [{"params": {"$elemMatch": {"$and": [{"name": {"$eq": "articleId"}}, {"val": {"$eq": "FOO-FOO-ARTICLE-ID-FOO"}}]}}}, {"params": {"$elemMatch": {"$and": [{"name": {"$eq": "brand"}}, {"val": {"$eq":"FooManufacturer"}}]}}}]}, {"$and": [{"params": {"$elemMatch": {"$and": [{"name": {"$eq": "articleId"}}, {"val": {"$eq": "FOO-FOO-ARTICLE-ID-FOO"}}]}}}, {"params": {"$elemMatch": {"$and": [{"name": {"$eq": "manufacturer"}}, {"val": {"$eq": "FooManufacturer"}}]}}}]}]}, "nReturned": new NumberInt("1"), "executionTimeMillisEstimate": new NumberInt("0"), "works": new NumberInt("2"), "advanced": new NumberInt("1"), "needTime": new NumberInt("0"), "needYield": new NumberInt("0"), "saveState": new NumberInt("0"), "restoreState": new NumberInt("0"), "isEOF": new NumberInt("1"), "docsExamined": new NumberInt("1"), "alreadyHasObj": new NumberInt("0"), "inputStage": {"stage": "IXSCAN", "nReturned": new NumberInt("1"), "executionTimeMillisEstimate": new NumberInt("0"), "works": new NumberInt("2"), "advanced": new NumberInt("1"), "needTime": new NumberInt("0"), "needYield": new NumberInt("0"), "saveState": new NumberInt("0"), "restoreState": new NumberInt("0"), "isEOF": new NumberInt("1"), "keyPattern": {"params.name": new NumberInt("1"), "params.val": new NumberInt("1")}, "indexName": "params.name_1_params.val_1", "collation": {"locale": "en", "caseLevel": false, "caseFirst": "off", "strength": new NumberInt("1"), "numericOrdering": false, "alternate": "shifted", "maxVariable": "punct", "normalization": false, "backwards": false, "version": "57.1"}, "isMultiKey": true, "multiKeyPaths": {"params.name": ["params"], "params.val": ["params"]}, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": new NumberInt("2"), "direction": "forward", "indexBounds": {"params.name": ["[CollationKey(0xCOLLECTION_KEY), CollationKey(0xCOLLECTION_KEY)]"], "params.val": ["[CollationKey(0xCOLLECTION_KEY_2), CollationKey(0xCOLLECTION_KEY_2)]"]}, "keysExamined": new NumberInt("1"), "seeks": new NumberInt("1"), "dupsTested": new NumberInt("1"), "dupsDropped": new NumberInt("0")}}}}
and this is interesting - from app logs (same query - same db, executed via spring)
"planSummary": "IXSCAN { params.name: 1, params.val: 1 }, IXSCAN { params.name: 1, params.val: 1 }", "keysExamined": 87182, "docsExamined": 87182, "cursorExhausted": true, "numYields": 484, "nreturned": 1,
I cannot understand how the result of the same query is different in each case. I tried to use an older version of the mongo driver in dataGrip because I thought maybe it was outdated in the application - but it didn’t help. Still hand-made is precise and fast. Both queries (manual and in-app) using same index.