Using Multiple Indexes Per Collection
source link: http://jsteemann.github.io/blog/2015/11/20/using-multiple-indexes-per-collection/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Using Multiple Indexes Per Collection
Nov 20th, 2015
The query optimizer in ArangoDB 2.8 has been improved in terms of how it can make use of indexes. In previous versions of ArangoDB, the query optimizer could use only one index per collection used in an AQL query. When using a logical OR in a FILTER condition, the optimizer did not use any index for the collection in order to ensure the result is still correct.
This is much better in 2.8. Now the query optimizer can use multiple indexes on the same collection for FILTER conditions that are combined with a logical OR.
For all following queries, I have set up a collection named test
, which has
two isolated hash indexes on the attributes value1
and value2
, and a skiplist
index on attribute value3
.
Let’s first try an AQL queries that uses a logical OR on two different attributes of the collection:
example query
|
The execution plan for this query in 2.7 reveals that query will perform a full collection scan and cannot use indexes because of the logical OR on two different attributes:
2.7 query execution plan
|
Running the same query in 2.8 / devel will produce a much better execution plan:
2.8 query execution plan
|
Multiple indexes will also be used if different index types are accessed, or for non-equality filter conditions. For example, the following query will make use of the two hash indexes and also the skiplist index:
example query
|
Here is its execution plan from 2.8:
2.8 query execution plan
|
For comparison, here is the non-optimized plan from 2.7 for the same query:
2.7 query execution plan
|
Still the query optimizer will not be able to use any indexes on a collection when there are multiple FILTER conditions combined with logical OR and at least one of them is not satisfisable by an index of the collection. In this case it has no other choice but to do a full collection scan.
For queries that combine multiple FILTER conditions with a logical AND, the optimizer will still try to pick the most selective index for the query and use it for the collection.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK