AQL Query Optimization with Query Profiling
Simple AQL query
Assuming we got a collection named collection and insert 100000 documents via for (let i=0; i < 100000;i++) db.collection.insert({value:i}). Then a simple query filtering for value < 10 should return 10 results:
FOR doc IN collection FILTER doc.value < 10 RETURN doc
An AQL query is essentially executed in a pipeline that chains togehter different functional execution nodes. Each block gets the input rows from it’s parent, does some processing and then outputs a certain number of output rows.
Without any indexes this query should have to perform the following operations:
- Perfom a full collection scan via a EnumerateCollectionNode and outputing a row containing the document in
doc. - Calculate the boolean expression
LET #1 = doc.value < 10from all inputs via a CalculationNode - Filter out all input rows where
#1is false via the FilterNode
Put thedocvariable of the remaining rows into the result set via the ResultNode
Without any detailed insight into the query execution it is impossible to tell how many results each stage had to work on and how long this took. By executing the query with the query profiler (db._profileQuery or via the Profile button in the WebUI) you can now tell exactly how much work each stage had to do:
The EnumerateCollectionNode processed and returned all 100k rows (documents), as did the CalculationNode. Because the AQL execution engine also uses an internal batch size of 1000 these blocks were also called 100 times each. The FilterNode as well as the ReturnNode however only ever returned 10 rows and only had to be called once, because the result size fits within a single batch.
Now lets add a skiplist index on value to speedup the query.
db.test.ensureIndex({type:"skiplist", fields:["value"]})
The results in replacing the collection scan and filter block with an IndexNode. The execution pipeleine of the AQL query has become much shorter. Also the number of rows processed by each pipeline block is only 10, because we no longer need to look at all documents.
AQL with aggregation
Let’s try a more advanced example, using a COLLECT statement. Assuming we have a user collection with a city, username and an age value:
db._create("users");
["berlin", "paris", "cologne", "munich", "london"].forEach((c) => {
["peter", "david", "simon", "lars"].forEach(
n => db.users.insert({ city: c, name: n, age: Math.floor(Math.random() * 75) })
)
});
Now lets try a query which gets us all age groups in buckets (0-9, 10-19, 20-29, …)
FOR u IN users
COLLECT ageGroup = FLOOR(u.age / 10) * 10
AGGREGATE minAge = MIN(u.age), maxAge = MAX(u.age), len = LENGTH(u)
RETURN {
ageGroup,
minAge,
maxAge,
len
}
Without any indexes this query should have to perform the following operations:
- Perfom a full collection scan via a EnumerateCollectionNode and outputing a row containg the document in
doc. - Calculate expressions
LET #1 = FLOOR(u.age / 10) * 10, etc from all inputs via multiple CalculationNode - Perform the aggregations via the CollectNode
- Sort the resulting aggregated rows via a SortNode
- Build a result value via another CalculationNode
- Put the result variable into the result set via the ResultNode
Like within the example above, you can see that after the CalculationNode stage, from the originally 20 rows only a handful remained.