{ "subject" : "ArangoDB", "tags": [ "multi-model", "nosql", "database" ] }

Explaining AQL Queries the Fancier Way

I have been looking at many AQL queries during the last few weeks.

Looking back, I can say that the JSON query execution plans provided by the explain() method have provided me with a lot of useful information about how the AQL optimizer had transformed a given query. This has helped testing and improving the query optimizer a great deal.

However, the JSON output produced by explain() is so detailed that even for the simplest cases queries it will span multiple screens. This is far too much for quicking assesing what a query will be doing and how it will be executed.

I therefore quickly put together a function that provides a much more compact explain output. Its input parameter is a query string, which it will send to the ArangoDB server to have it explained.

But it doesn’t print a voluminous JSON object. This one is for developers with a full schedule.

It prints the original query, the generated query execution plan, the applied optimizer rules plus the list of indexes that will be used. This is often the key information that one is interested in.

It works best when the execution plan fits on a single screen and the execution plan is not nested too much. Subqueries are supported though. I have tried it with several queries this night and the results were quite promising from my point of view.

For example, a poor man’s graph query (using a subquery to join connected edges) still prints nicely:

As usual, your mileage may vary. If you’re inspecting complex queries with it, the lines printed may get too long and wrap. The output won’t be very legible then, but it may still be helpful.

I have added the feature to 2.4 and devel. Anyone compiling from source can use it by pulling the latest changes, compiling, and then typing the following on the ArangoShell or in the web interface:

var explain = require("org/arangodb/aql/explainer").explain;
explain("FOR doc IN myCollection FILTER doc.value < 23 RETURN doc");

The feature will also be included in the next 2.4 package.

Anyone on 2.3 or 2.4 users that can’t wait until the next package can give it a try, too. The code for the explain function is contained in a single file, and it can be downloaded from Github. and then be copied into the appropriate location inside ArangoDB’s js directory. This should make it work from the ArangoShell (but not from the web interface).

On a side note: I would also like to have some functionality like this in the web interface, but as a backend developer, I am not able to do any serious UI work. I also put this together in my leisure time, and have to come to an end with it.