-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Minor - P4
-
None
-
Affects Version/s: 3.4.0
-
Component/s: Aggregation Framework
-
None
-
Query Optimization
-
(copied to CRM)
I think unwind stage can be avoid when aggregation query only need index field (multi-key index field). And Also aggregation grouping or sorting stage can be solved via sorted index without quick sort processing. (I assume multi-key index has same structure with normal b-tree index.)
We have below collections which has array field and multi-key index on that field.
db.mkey.insert({ "_id" : 1, "apps": [ "A", "B", "C"] }) db.mkey.insert({ "_id" : 2, "apps": ["A"] }) db.mkey.insert({ "_id" : 3, "apps": ["A", "C"] }) db.mkey.createIndex({apps:1})
And we want to aggregate apps and count for each app.
db.mkey.aggregate([ {'$unwind': '$apps'}, {'$group': { _id: '$apps', sum: { '$sum': 1 } } }, {'$match': { sum: { '$gte': 2 } } } ]) { "_id" : "A", "sum" : 3 } { "_id" : "C", "sum" : 2 }
This aggregation query can't use multi-key index and need to expand (unwind) apps field of all documents and sort.
But if this query can use multi-key index of apps field, aggregation can be processed via multi-key index only not data files and no unwind and no quick sort processing. And I think this can be much faster than full collection scan.
- duplicates
-
SERVER-54427 DISTINCT_SCAN can be used with compound index when filter is on non-multikey field
- Backlog
- is related to
-
SERVER-35223 $elemMatch forces mongo to fetch documents instead of using COUNT_SCAN
- Closed
-
SERVER-9370 Optimize $match $unwind $match sequence in pipeline
- Closed
- related to
-
SERVER-37715 Use DISTINCT_SCAN for $unwind-$group pipelines
- Backlog