If it is unclear how a given query will perform, clients can retrieve a query’s execution plan
from the AQL query optimizer without actually executing the query. Getting the query execution
plan from the optimizer is called explaining.
An explain will throw an error if the given query is syntactically invalid. Otherwise, it will
return the execution plan and some information about what optimizations could be applied to
the query. The query will not be executed.
Explaining a query can be achieved by calling the HTTP REST API
or via arangosh.
A query can also be explained from the ArangoShell using the ArangoDatabase’s explain method
or in detail via ArangoStatement’s explain method.
The explain method of ArangoStatement as shown in the next chapters creates very verbose output.
To get a human-readable output of the query plan you can use the explain method on our database
object in arangosh. You may use it like this: (we disable syntax highlighting here)
arangosh> db._explain("LET s = SLEEP(0.25) LET t = SLEEP(0.5) RETURN 1", {}, {colors: false});
Show execution results
Hide execution results
Query String (47 chars, cacheable: false):
LET s = SLEEP(0.25) LET t = SLEEP(0.5) RETURN 1
Execution plan:
Id NodeType Est. Comment
1 SingletonNode 1 * ROOT
4 CalculationNode 1 - LET #2 = 1 /* json expression */ /* const assignment */
2 CalculationNode 1 - LET s = SLEEP(0.25) /* simple expression */
3 CalculationNode 1 - LET t = SLEEP(0.5) /* simple expression */
5 ReturnNode 1 - RETURN #2
Indexes used:
none
Functions used:
Name Deterministic Cacheable Uses V8
SLEEP false false false
Optimization rules applied:
Id RuleName
1 move-calculations-up
41 rule(s) executed, 1 plan(s) created
The plan contains all execution nodes that are used during a query. These nodes represent different
stages in a query. Each stage gets the input from the stage directly above (its dependencies).
The plan will show you the estimated number of items (results) for each query stage (under Est.). Each
query stage roughly equates to a line in your original query, which you can see under Comment.
Sometimes when you have a complex query it can be unclear on what time is spent
during the execution, even for intermediate ArangoDB users.
By profiling a query it gets executed with special instrumentation code enabled.
It gives you all the usual information like when explaining a query, but
additionally you get the query profile, runtime statistics
and per-node statistics.
To use this in an interactive fashion on the shell you can use the
_profileQuery() method on the ArangoDatabase object or use the web interface.
By default, the query optimizer will return what it considers to be the optimal plan. The
optimal plan will be returned in the plan attribute of the result. If explain is
called with option allPlans set to true, all plans will be returned in the plans
attribute instead. The result object will also contain an attribute warnings, which
is an array of warnings that occurred during optimization or execution plan creation.
Each plan in the result is an object with the following attributes:
In some cases the AQL optimizer creates multiple plans for a single query. By default
only the plan with the lowest total estimated cost is kept, and the other plans are
discarded. To retrieve all plans the optimizer has generated, explain can be called
with the option allPlans set to true.
In the following example, the optimizer has created two plans:
explain will also accept the following additional options:
maxPlans: limits the maximum number of plans that are created by the AQL query optimizer
optimizer.rules: an array of to-be-included or to-be-excluded optimizer rules
can be put into this attribute, telling the optimizer to include or exclude
specific rules. To disable a rule, prefix its name with a -, to enable a rule, prefix it
with a +. There is also a pseudo-rule all, which will match all optimizer rules.
The following example disables all optimizer rules but remove-redundant-calculations:
The contents of an execution plan are meant to be machine-readable. To get a human-readable
version of a query’s execution plan, the following commands can be used:
arangosh> var query = "FOR doc IN mycollection FILTER doc.value > 42 RETURN doc";
arangosh> require("@arangodb/aql/explainer").explain(query, {colors:false});
Show execution results
Hide execution results
Query String (56 chars, cacheable: true):
FOR doc IN mycollection FILTER doc.value > 42 RETURN doc
Execution plan:
Id NodeType Est. Comment
1 SingletonNode 1 * ROOT
2 EnumerateCollectionNode 302 - FOR doc IN mycollection /* full collection scan */ FILTER (doc.`value` > 42) /* early pruning */5 ReturnNode 302 - RETURN doc
Indexes used:
none
Optimization rules applied:
Id RuleName
1 move-filters-into-enumerate
41 rule(s) executed, 1 plan(s) created
The above command prints the query’s execution plan in the ArangoShell
directly, focusing on the most important information.
Gathering debug information about a queryPermalink
If an explain provides no suitable insight into why a query does not perform as
expected, it may be reported to the ArangoDB support. In order to make this as easy
as possible, there is a built-in command in ArangoShell for packaging the query, its
bind parameters and all data required to execute the query elsewhere.
The command will store all data in a file with a configurable filename:
arangosh> var query = "FOR doc IN @@collection FILTER doc.value > @value RETURN doc";
arangosh> var bind = { value: 42, "@collection": "mycollection" };
arangosh> require("@arangodb/aql/explainer").debugDump("/tmp/query-debug-info", query, bind);
Show execution results
Hide execution results
It is also possible to include example documents from the underlying collection in
order to make reproduction even easier. Example documents can be sent as they are, or
in an anonymized form. The number of example documents can be specified in the examples
options attribute, and should generally be kept low. The anonymize option will replace
the contents of string attributes in the examples with “XXX”. It will however not
replace any other types of data (e.g. numeric values) or attribute names. Attribute
names in the examples will always be preserved because they may be indexed and used in
queries:
