Working with Indexes

Learn how to use different indexes efficiently by going through theArangoDB Performance Course.

Index Identifiers and Handles

An index handle uniquely identifies an index in the database. It is a string andconsists of the collection name and an index identifier separated by a /. Theindex identifier part is a numeric value that is auto-generated by ArangoDB.

A specific index of a collection can be accessed using its index handle orindex identifier as follows:

  1. db.collection.index("<index-handle>");
  2. db.collection.index("<index-identifier>");
  3. db._index("<index-handle>");

For example: Assume that the index handle, which is stored in the _idattribute of the index, is demo/362549736 and the index was created in a collectionnamed demo. Then this index can be accessed as:

  1. db.demo.index("demo/362549736");

Because the index handle is unique within the database, you can leave out thecollection and use the shortcut:

  1. db._index("demo/362549736");

An index may also be looked up by its name. Since names are only unique withina collection, rather than within the database, the lookup must also include thecollection name.

  1. db._index("demo/primary")
  2. db.demo.index("primary")

Collection Methods

Listing all indexes of a collection

returns information about the indexesgetIndexes()

Returns an array of all indexes defined for the collection.Since ArangoDB 3.4, indexes() is an alias for getIndexes().

Note that _key implicitly has an index assigned to it.

  1. arangosh> db.test.ensureHashIndex("hashListAttribute",
  2. ........> "hashListSecondAttribute.subAttribute");
  3. arangosh> db.test.getIndexes();

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "hashListAttribute", 
  5.     "hashListSecondAttribute.subAttribute" 
  6.   ], 
  7.   "id" : "test/73798", 
  8.   "isNewlyCreated" : true, 
  9.   "name" : "idx_1642473900108414976", 
  10.   "selectivityEstimate" : 1, 
  11.   "sparse" : false, 
  12.   "type" : "hash", 
  13.   "unique" : false, 
  14.   "code" : 201 
  15. }
  16. [ 
  17.   { 
  18.     "fields" : [ 
  19.       "_key" 
  20.     ], 
  21.     "id" : "test/0", 
  22.     "name" : "primary", 
  23.     "selectivityEstimate" : 1, 
  24.     "sparse" : false, 
  25.     "type" : "primary", 
  26.     "unique" : true 
  27.   }, 
  28.   { 
  29.     "deduplicate" : true, 
  30.     "fields" : [ 
  31.       "skiplistAttribute" 
  32.     ], 
  33.     "id" : "test/73790", 
  34.     "name" : "idx_1642473900107366400", 
  35.     "selectivityEstimate" : 1, 
  36.     "sparse" : false, 
  37.     "type" : "skiplist", 
  38.     "unique" : true 
  39.   }, 
  40.   { 
  41.     "deduplicate" : true, 
  42.     "fields" : [ 
  43.       "skiplistUniqueAttribute" 
  44.     ], 
  45.     "id" : "test/73794", 
  46.     "name" : "idx_1642473900107366401", 
  47.     "selectivityEstimate" : 1, 
  48.     "sparse" : false, 
  49.     "type" : "skiplist", 
  50.     "unique" : true 
  51.   }, 
  52.   { 
  53.     "deduplicate" : true, 
  54.     "fields" : [ 
  55.       "hashListAttribute", 
  56.       "hashListSecondAttribute.subAttribute" 
  57.     ], 
  58.     "id" : "test/73798", 
  59.     "name" : "idx_1642473900108414976", 
  60.     "selectivityEstimate" : 1, 
  61.     "sparse" : false, 
  62.     "type" : "hash", 
  63.     "unique" : false 
  64.   } 
  65. ]

Hide execution results

Creating an index

Indexes should be created using the general method ensureIndex. Thismethod obsoletes the specialized index-specific methods ensureHashIndex,ensureSkiplist, ensureUniqueConstraint etc.ensures that an index existscollection.ensureIndex(index-description)

Ensures that an index according to the index-description exists. Anew index will be created if none exists with the given description.

The index-description must contain at least a type attribute.Other attributes may be necessary, depending on the index type.

type can be one of the following values:

  • hash: hash index
  • skiplist: skiplist index
  • fulltext: fulltext index
  • geo: geo index, with one or two attributesname can be a string. Index names are subject to the same characterrestrictions as collection names. If omitted, a name will be auto-generated sothat it is unique with respect to the collection, e.g. idx_832910498.

sparse can be true or false.

For hash, and skiplist the sparsity can be controlled, fulltext and _geo_are sparse by definition.

unique can be true or false and is supported by hash or skiplist

Calling this method returns an index object. Whether or not the indexobject existed before the call is indicated in the return attributeisNewlyCreated.

deduplicate can be true or false and is supported by array indexes oftype hash or skiplist. It controls whether inserting duplicate index valuesfrom the same document into a unique array index will lead to a unique constrainterror or not. The default value is true, so only a single instance of eachnon-unique index value will be inserted into the index per document. Trying toinsert a value into the index that already exists in the index will always fail,regardless of the value of this attribute.

Examples

  1. arangosh> db.test.ensureIndex({ type: "hash", fields: [ "a" ], sparse: true });
  2. arangosh> db.test.ensureIndex({ type: "hash", fields: [ "a", "b" ], unique: true });

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "a" 
  5.   ], 
  6.   "id" : "test/73739", 
  7.   "isNewlyCreated" : true, 
  8.   "name" : "idx_1642473900054937600", 
  9.   "selectivityEstimate" : 1, 
  10.   "sparse" : true, 
  11.   "type" : "hash", 
  12.   "unique" : false, 
  13.   "code" : 201 
  14. }
  15. { 
  16.   "deduplicate" : true, 
  17.   "fields" : [ 
  18.     "a", 
  19.     "b" 
  20.   ], 
  21.   "id" : "test/73743", 
  22.   "isNewlyCreated" : true, 
  23.   "name" : "idx_1642473900054937601", 
  24.   "selectivityEstimate" : 1, 
  25.   "sparse" : false, 
  26.   "type" : "hash", 
  27.   "unique" : true, 
  28.   "code" : 201 
  29. }

Hide execution results

Dropping an index via a collection handle

drops an indexcollection.dropIndex(index)

Drops the index. If the index does not exist, then false isreturned. If the index existed and was dropped, then true isreturned. Note that you cannot drop some special indexes (e.g. the primaryindex of a collection or the edge index of an edge collection).

collection.dropIndex(index-handle)

Same as above. Instead of an index an index handle can be given.

  1. arangosh> db.example.ensureSkiplist("a", "b");
  2. arangosh> var indexInfo = db.example.getIndexes();
  3. arangosh> indexInfo;
  4. arangosh> db.example.dropIndex(indexInfo[0])
  5. arangosh> db.example.dropIndex(indexInfo[1].id)
  6. arangosh> indexInfo = db.example.getIndexes();

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "a", 
  5.     "b" 
  6.   ], 
  7.   "id" : "example/73584", 
  8.   "isNewlyCreated" : true, 
  9.   "name" : "idx_1642473897699835904", 
  10.   "selectivityEstimate" : 1, 
  11.   "sparse" : false, 
  12.   "type" : "skiplist", 
  13.   "unique" : false, 
  14.   "code" : 201 
  15. }
  16. [ 
  17.   { 
  18.     "fields" : [ 
  19.       "_key" 
  20.     ], 
  21.     "id" : "example/0", 
  22.     "name" : "primary", 
  23.     "selectivityEstimate" : 1, 
  24.     "sparse" : false, 
  25.     "type" : "primary", 
  26.     "unique" : true 
  27.   }, 
  28.   { 
  29.     "deduplicate" : true, 
  30.     "fields" : [ 
  31.       "a", 
  32.       "b" 
  33.     ], 
  34.     "id" : "example/73584", 
  35.     "name" : "idx_1642473897699835904", 
  36.     "selectivityEstimate" : 1, 
  37.     "sparse" : false, 
  38.     "type" : "skiplist", 
  39.     "unique" : false 
  40.   } 
  41. ]
  42. false
  43. true
  44. [ 
  45.   { 
  46.     "fields" : [ 
  47.       "_key" 
  48.     ], 
  49.     "id" : "example/0", 
  50.     "name" : "primary", 
  51.     "selectivityEstimate" : 1, 
  52.     "sparse" : false, 
  53.     "type" : "primary", 
  54.     "unique" : true 
  55.   } 
  56. ]

Hide execution results

Load Indexes into Memory

Loads all indexes of this collection into Memory.collection.loadIndexesIntoMemory()

This function tries to cache all index entriesof this collection into the main memory.Therefore it iterates over all indexes of the collectionand stores the indexed values, not the entire document data,in memory.All lookups that could be found in the cache are much fasterthan lookups not stored in the cache so you get a nice performance boost.It is also guaranteed that the cache is consistent with the stored data.

For the time being this function is only useful on RocksDB storage engine,as in MMFiles engine all indexes are in memory anyways.

On RocksDB this function honors all memory limits, if the indexes you wantto load are smaller than your memory limit this function guarantees that mostindex values are cached.If the index is larger than your memory limit this function will fill up valuesup to this limit and for the time being there is no way to control which indexesof the collection should have priority over others.

  1. arangosh> db.example.loadIndexesIntoMemory();

Show execution results

  1. { 
  2.   "result" : true 
  3. }

Hide execution results

Database Methods

Fetching an index by handle

finds an indexdb._index(index-handle)

Returns the index with index-handle or null if no such index exists.

  1. arangosh> db.example.ensureIndex({ type: "skiplist", fields: [ "a", "b" ] });
  2. arangosh> var indexInfo = db.example.getIndexes().map(function(x) { return x.id; });
  3. arangosh> indexInfo;
  4. arangosh> db._index(indexInfo[0])
  5. arangosh> db._index(indexInfo[1])

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "a", 
  5.     "b" 
  6.   ], 
  7.   "id" : "example/68943", 
  8.   "isNewlyCreated" : true, 
  9.   "name" : "idx_1642473881181618178", 
  10.   "selectivityEstimate" : 1, 
  11.   "sparse" : false, 
  12.   "type" : "skiplist", 
  13.   "unique" : false, 
  14.   "code" : 201 
  15. }
  16. [ 
  17.   "example/0", 
  18.   "example/68943" 
  19. ]
  20. { 
  21.   "fields" : [ 
  22.     "_key" 
  23.   ], 
  24.   "id" : "example/0", 
  25.   "name" : "primary", 
  26.   "sparse" : false, 
  27.   "type" : "primary", 
  28.   "unique" : true, 
  29.   "code" : 200 
  30. }
  31. { 
  32.   "deduplicate" : true, 
  33.   "fields" : [ 
  34.     "a", 
  35.     "b" 
  36.   ], 
  37.   "id" : "example/68943", 
  38.   "name" : "idx_1642473881181618178", 
  39.   "sparse" : false, 
  40.   "type" : "skiplist", 
  41.   "unique" : false, 
  42.   "code" : 200 
  43. }

Hide execution results

Dropping an index via a database handle

drops an indexdb._dropIndex(index)

Drops the index. If the index does not exist, then false isreturned. If the index existed and was dropped, then true isreturned.

db._dropIndex(index-handle)

Drops the index with index-handle.

  1. arangosh> db.example.ensureIndex({ type: "skiplist", fields: [ "a", "b" ] });
  2. arangosh> var indexInfo = db.example.getIndexes();
  3. arangosh> indexInfo;
  4. arangosh> db._dropIndex(indexInfo[0])
  5. arangosh> db._dropIndex(indexInfo[1].id)
  6. arangosh> indexInfo = db.example.getIndexes();

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "a", 
  5.     "b" 
  6.   ], 
  7.   "id" : "example/74348", 
  8.   "isNewlyCreated" : true, 
  9.   "name" : "idx_1642473900915818496", 
  10.   "selectivityEstimate" : 1, 
  11.   "sparse" : false, 
  12.   "type" : "skiplist", 
  13.   "unique" : false, 
  14.   "code" : 201 
  15. }
  16. [ 
  17.   { 
  18.     "fields" : [ 
  19.       "_key" 
  20.     ], 
  21.     "id" : "example/0", 
  22.     "name" : "primary", 
  23.     "selectivityEstimate" : 1, 
  24.     "sparse" : false, 
  25.     "type" : "primary", 
  26.     "unique" : true 
  27.   }, 
  28.   { 
  29.     "deduplicate" : true, 
  30.     "fields" : [ 
  31.       "a", 
  32.       "b" 
  33.     ], 
  34.     "id" : "example/74348", 
  35.     "name" : "idx_1642473900915818496", 
  36.     "selectivityEstimate" : 1, 
  37.     "sparse" : false, 
  38.     "type" : "skiplist", 
  39.     "unique" : false 
  40.   } 
  41. ]
  42. false
  43. true
  44. [ 
  45.   { 
  46.     "fields" : [ 
  47.       "_key" 
  48.     ], 
  49.     "id" : "example/0", 
  50.     "name" : "primary", 
  51.     "selectivityEstimate" : 1, 
  52.     "sparse" : false, 
  53.     "type" : "primary", 
  54.     "unique" : true 
  55.   } 
  56. ]

Hide execution results

Revalidating whether an index is used

finds an index

So you’ve created an index, and since its maintainance isn’t for free,you definitely want to know whether your query can utilize it.

You can use explain to verify whether skiplists or hash indexes areused (if you omit colors: false you will get nice colors in ArangoShell):

  1. arangosh> var explain = require("@arangodb/aql/explainer").explain;
  2. arangosh> db.example.ensureIndex({ type: "skiplist", fields: [ "a", "b" ] });
  3. arangosh> explain("FOR doc IN example FILTER doc.a < 23 RETURN doc", {colors:false});

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "a", 
  5.     "b" 
  6.   ], 
  7.   "id" : "example/68957", 
  8.   "isNewlyCreated" : true, 
  9.   "name" : "idx_1642473881187909634", 
  10.   "selectivityEstimate" : 1, 
  11.   "sparse" : false, 
  12.   "type" : "skiplist", 
  13.   "unique" : false, 
  14.   "code" : 201 
  15. }
  16. Query String:
  17.  FOR doc IN example FILTER doc.a < 23 RETURN doc
  18.  
  19. Execution plan:
  20.  Id   NodeType        Est.   Comment
  21.   1   SingletonNode      1   * ROOT
  22.   6   IndexNode          0     - FOR doc IN example   /* skiplist index scan */
  23.   5   ReturnNode         0       - RETURN doc
  24.  
  25. Indexes used:
  26.  By   Name                      Type       Collection   Unique   Sparse   Selectivity   Fields         Ranges
  27.   6   idx_1642473881187909634   skiplist   example      false    false       100.00 %   [ `a`, `b` ]   (doc.`a` < 23)
  28.  
  29. Optimization rules applied:
  30.  Id   RuleName
  31.   1   use-indexes
  32.   2   remove-filter-covered-by-index
  33.   3   remove-unnecessary-calculations-2

Hide execution results