i have a performance issue with aggregations in ArangoDB in a large collection (20 mln documents)
profile output:
Query String (169 chars, cacheable: false):
FOR doc IN events
FILTER doc.date > '2021-07-28T00:00:00'
COLLECT type = doc.type WITH COUNT INTO cnt
RETURN {
"type": type,
"cnt": cnt
}
Execution plan:
Id NodeType Calls Items Runtime [s] Comment
1 SingletonNode 1 1 0.00000 * ROOT
10 IndexNode 1471 1470991 0.78432 - FOR doc IN events /* persistent index scan, index only, projections: `type` */
5 CalculationNode 1471 1470991 0.34793 - LET #5 = doc.`type` /* attribute expression */ /* collections used: doc : events */
6 CollectNode 1 48 0.22653 - COLLECT type = #5 AGGREGATE cnt = LENGTH() /* hash */
9 SortNode 1 48 0.00005 - SORT type ASC /* sorting strategy: standard */
7 CalculationNode 1 48 0.00005 - LET #7 = { "type" : type, "cnt" : cnt } /* simple expression */
8 ReturnNode 1 48 0.00000 - RETURN #7
Indexes used:
By Name Type Collection Unique Sparse Selectivity Fields Ranges
10 date_type persistent events false false 40.11 % [ `date`, `type` ] (doc.`date` > "2021-07-28T00:00:00")
Optimization rules applied:
Id RuleName
1 move-calculations-up
2 move-filters-up
3 move-calculations-up-2
4 move-filters-up-2
5 use-indexes
6 remove-filter-covered-by-index
7 remove-unnecessary-calculations-2
8 move-calculations-down
9 reduce-extraction-to-projection
Query Statistics:
Writes Exec Writes Ign Scan Full Scan Index Filtered Peak Mem [b] Exec Time [s]
0 0 0 1470991 0 196608 1.35927
Query Profile:
Query Stage Duration [s]
initializing 0.00000
parsing 0.00004
optimizing ast 0.00001
loading collections 0.00001
instantiating plan 0.00002
optimizing plan 0.00029
executing 1.35889
finalizing 0.00003
there are 50 event.type and RETURN DISTINCT doc.type is also running slow
i tried to add an index on field type but things got even worse.
tried to create [type, date] and add SORT doc.type
Query String (187 chars, cacheable: false):
FOR doc IN events
SORT doc.type
FILTER doc.date > '2021-07-28T00:00:00'
COLLECT type = doc.type WITH COUNT INTO cnt
RETURN {
"type": type,
"cnt": cnt
}
Execution plan:
Id NodeType Calls Items Runtime [s] Comment
1 SingletonNode 1 1 0.00000 * ROOT
12 IndexNode 1471 1470991 17.29910 - FOR doc IN events /* persistent index scan, index only, projections: `date`, `type` */ FILTER (doc.`date` > "2021-07-28T00:00:00") /* early pruning */
3 CalculationNode 1471 1470991 0.36269 - LET #3 = doc.`type` /* attribute expression */ /* collections used: doc : events */
8 CollectNode 1 48 0.17226 - COLLECT type = #3 AGGREGATE cnt = LENGTH() /* sorted */
9 CalculationNode 1 48 0.00005 - LET #9 = { "type" : type, "cnt" : cnt } /* simple expression */
10 ReturnNode 1 48 0.00001 - RETURN #9
Indexes used:
By Name Type Collection Unique Sparse Selectivity Fields Ranges
12 type_date persistent events false false 39.97 % [ `type`, `date` ] *
Optimization rules applied:
Id RuleName
1 move-calculations-up
2 move-filters-up
3 remove-redundant-calculations
4 remove-unnecessary-calculations
5 remove-redundant-sorts
6 move-calculations-up-2
7 use-indexes
8 use-index-for-sort
9 move-calculations-down
10 reduce-extraction-to-projection
11 move-filters-into-enumerate
Query Statistics:
Writes Exec Writes Ign Scan Full Scan Index Filtered Peak Mem [b] Exec Time [s]
0 0 0 21712260 20241269 327680 17.83480
Query Profile:
Query Stage Duration [s]
initializing 0.00000
parsing 0.00004
optimizing ast 0.00001
loading collections 0.00001
instantiating plan 0.00003
optimizing plan 0.00034
executing 17.83435
finalizing 0.00003
is it possible to speed it up ?