Index Utilization

    Creating multiple indexes on different attributes of the same collection may give the query optimizer more choices when picking an index. Creating multiple indexes on different attributes can also help in speeding up different queries, with FILTER conditions on different attributes.

    It is often beneficial to create an index on more than just one attribute. By adding more attributes to an index, an index can become more selective and thus reduce the number of documents that queries need to process.

    ArangoDB’s primary indexes, edges indexes and hash indexes will automatically provide selectivity estimates. Index selectivity estimates are provided in the web interface, the getIndexes() return value and in the explain() output for a given query.

    The more selective an index is, the more documents it will filter on average. The index selectivity estimates are therefore used by the optimizer when creating query execution plans when there are multiple indexes the optimizer can choose from. The optimizer will then select a combination of indexes with the lowest estimated total cost. In general, the optimizer will pick the indexes with the highest estimated selectivity.

    When in doubt about whether and which indexes will be used for executing a given AQL query, click the Explain button in the web interface in the Queries view or use the explain() method for the statement as follows (from the ArangoShell):

    The explain() command will return a detailed JSON representation of the query’s execution plan. The JSON explain output is intended to be used by code. To get a human-readable and much more compact explanation of the query, there is an explainer tool:

    If any of the explain methods shows that a query is not using indexes, the following steps may help:

    • using indexed attributes as function parameters or in arbitrary expressions will likely lead to the index on the attribute not being used. For example, the following queries will not use an index on value:

      In these cases the queries should be rewritten so that only the index attribute is present on one side of the operator, or additional filters and indexes should be used to restrict the amount of documents otherwise.

    • The two ORs in the first query will be converted to an IN list, and if there is a suitable index on value1, it will be used. The second query requires two separate indexes on value1 and value2 and will use them if present. The third query can use the indexes on value1 and when they are sorted.