Working with Persistent Indexes

If a suitable persistent index exists, then /_api/simple/range and other operations will use this index to execute queries.

Create a persistent index

creates a persistent index

POST /_api/index

Query Parameters

• collection (string, required): The collection name.

Request Body

• type (string, required): must be equal to “persistent”.

• fields (array of strings, required): an array of attribute paths.

• unique (boolean, optional): if true, then create a unique index.

• sparse (boolean, optional): if true, then create a sparse index.

• deduplicate (boolean, optional): The attribute deduplicate is supported by array indexes of type persistent, hash or skiplist. It controls whether inserting duplicate index values from the same document into a unique array index will lead to a unique constraint error or not. The default value is true, so only a single instance of each non-unique index value will be inserted into the index per document. Trying to insert a value into the index that already exists in the index will always fail, regardless of the value of this attribute.

• estimates (boolean, optional): The attribute estimates is supported by indexes of type persistent. This attribute controls whether index selectivity estimates are maintained for the index. Not maintaining index selectivity estimates can have a slightly positive impact on write performance. The downside of turning off index selectivity estimates will be that the query optimizer will not be able to determine the usefulness of different competing indexes in AQL queries when there are multiple candidate indexes to choose from. The estimates attribute is optional and defaults to true if not set. It will have no effect on indexes other than persistent (with hash and skiplist being mere aliases for persistent nowadays).

• inBackground (boolean, optional): The optional attribute inBackground can be set to true to create the index in the background, which will not write-lock the underlying collection for as long as if the index is built in the foreground. The default value is false.

Creates a persistent index for the collection collection-name, if it does not already exist. The call expects an object containing the index details.

In a sparse index all documents will be excluded from the index that do not contain at least one of the specified index attributes (i.e. fields) or that have a value of null in any of the specified index attributes. Such documents will not be indexed, and not be taken into account for uniqueness checks if the unique flag is set.

In a non-sparse index, these documents will be indexed (for non-present indexed attributes, a value of null will be used) and will be taken into account for uniqueness checks if the unique flag is set.

Note: unique indexes on non-shard keys are not supported in a cluster.

Responses

HTTP 200: If the index already exists, then a HTTP 200 is returned.

HTTP 201: If the index does not already exist and could be created, then a HTTP 201 is returned.

HTTP 400: If the collection already contains documents and you try to create a unique persistent index in such a way that there are documents violating the uniqueness, then a HTTP 400 is returned.

HTTP 404: If the collection-name is unknown, then a HTTP 404 is returned.

Examples

Creating a persistent index

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "fields" : [ 
    "a", 
    "b" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
connection: Keep-Alive
content-length: 232
server: ArangoDB
x-content-type-options: nosniff

{ 
  "deduplicate" : true, 
  "estimates" : true, 
  "fields" : [ 
    "a", 
    "b" 
  ], 
  "id" : "products/68187", 
  "isNewlyCreated" : true, 
  "name" : "idx_1761718534531973120", 
  "selectivityEstimate" : 1, 
  "sparse" : false, 
  "type" : "persistent", 
  "unique" : false, 
  "error" : false, 
  "code" : 201 
}

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "fields" : [ 
    "a", 
    "b" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
connection: Keep-Alive
content-length: 232
server: ArangoDB
x-content-type-options: nosniff

Creating a sparse persistent index

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "sparse" : true, 
  "fields" : [ 
    "a" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
connection: Keep-Alive
content-length: 227
server: ArangoDB
x-content-type-options: nosniff

{ 
  "deduplicate" : true, 
  "estimates" : true, 
  "fields" : [ 
    "a" 
  ], 
  "id" : "products/68242", 
  "isNewlyCreated" : true, 
  "name" : "idx_1761718534543507457", 
  "selectivityEstimate" : 1, 
  "sparse" : true, 
  "type" : "persistent", 
  "unique" : false, 
  "error" : false, 
  "code" : 201 
}

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "sparse" : true, 
  "fields" : [ 
    "a" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
connection: Keep-Alive
content-length: 227
server: ArangoDB
x-content-type-options: nosniff