ArangoDB: Check which index is used while executing the queries

Using '@arangodb/aql/explainer' module, you can verify which index is used while executing a query.

 

Syntax

var explain = require("@arangodb/aql/explainer").explain;
explain("query to execute");

 

Let’s see it with an example.

 

Step 1: Let’s create an employees collection.

127.0.0.1:8529@abc_org> db._create("employees")
[ArangoCollection 1727, "employees" (type document, status loaded)]

 

Step 2: Let’s insert some data to employees collection.

127.0.0.1:8529@abc_org> db.employees.toArray()
[ 
  { 
    "_key" : "1768", 
    "_id" : "employees/1768", 
    "_rev" : "_cTmCyn----", 
    "id" : 1, 
    "name" : "Krishna", 
    "age" : 32 
  }, 
  { 
    "_key" : "1809", 
    "_id" : "employees/1809", 
    "_rev" : "_cTmDloy---", 
    "id" : 2, 
    "name" : "Narasimha", 
    "age" : 35 
  }, 
  { 
    "_key" : "1823", 
    "_id" : "employees/1823", 
    "_rev" : "_cTmD1ya---", 
    "id" : 3, 
    "name" : "Deeraj", 
    "age" : 38 
  }, 
  { 
    "_key" : "1837", 
    "_id" : "employees/1837", 
    "_rev" : "_cTmEHae---", 
    "id" : 4, 
    "name" : "Sailu", 
    "age" : 33 
  } 
]

Step 3: Let’s create an index on name attribute by executing below statement.

db.employees.ensureIndex({ type: "persistent", fields: [ "name" ], name: "employee_name_index" });

127.0.0.1:8529@abc_org> db.employees.ensureIndex({ type: "persistent", fields: [ "name" ], name: "employee_name_index" });
{ 
  "deduplicate" : true, 
  "fields" : [ 
    "name" 
  ], 
  "id" : "employees/1890", 
  "isNewlyCreated" : true, 
  "name" : "employee_name_index", 
  "selectivityEstimate" : 1, 
  "sparse" : false, 
  "type" : "persistent", 
  "unique" : false, 
  "code" : 201 
}

127.0.0.1:8529@abc_org> db.employees.getIndexes()
[ 
  { 
    "fields" : [ 
      "_key" 
    ], 
    "id" : "employees/0", 
    "name" : "primary", 
    "selectivityEstimate" : 1, 
    "sparse" : false, 
    "type" : "primary", 
    "unique" : true 
  }, 
  { 
    "deduplicate" : true, 
    "fields" : [ 
      "name" 
    ], 
    "id" : "employees/1890", 
    "name" : "employee_name_index", 
    "selectivityEstimate" : 1, 
    "sparse" : false, 
    "type" : "persistent", 
    "unique" : false 
  } 
]

Step 4: Let’s use explainer module to

 

var explain = require("@arangodb/aql/explainer").explain;

 

explain("FOR emp IN employees FILTER emp.name == 'Krishna' RETURN emp", {colors: false});

127.0.0.1:8529@abc_org> var explain = require("@arangodb/aql/explainer").explain;

127.0.0.1:8529@abc_org> explain("FOR emp IN employees FILTER emp.name == 'Krishna' RETURN emp", {colors: false});
Query String (60 chars, cacheable: true):
 FOR emp IN employees FILTER emp.name == 'Krishna' RETURN emp

Execution plan:
 Id   NodeType        Est.   Comment
  1   SingletonNode      1   * ROOT
  6   IndexNode          1     - FOR emp IN employees   /* persistent index scan */    
  5   ReturnNode         1       - RETURN emp

Indexes used:
 By   Name                  Type         Collection   Unique   Sparse   Selectivity   Fields       Ranges
  6   employee_name_index   persistent   employees    false    false       100.00 %   [ `name` ]   (emp.`name` == "Krishna")

Optimization rules applied:
 Id   RuleName
  1   use-indexes
  2   remove-filter-covered-by-index
  3   remove-unnecessary-calculations-2

From the output, you can confirm that ArangoDB uses ‘employee_name_index’ to execute queries on name attribute.

Previous                                                    Next                                                    Home