I suggest the following changes, but they won't speed up the query noticeably:
the filters FILTER r.field > null and FILTER r.field.v1 > 0 are redundant. You can just use the latter FILTER r.field.v1 > 0 and omit the other filter condition
the auxiliary variable LET relation = a.relation is defined after a.relation is used in the LENGTH(a.relation) calculation. If the auxiliary variable would be defined before the LENGTH() calculation, it could be used inside it like this: LET relation = a.relation FILTER LENGTH(relation) > 0. This will save a bit of processing time
the original query checks each v1 value and may return each document multiple times if multiple v1 values in a document satisfy the filter condition. That means the original query may return more documents than there are actually present in the collection. If that's not desired, I suggest using a subquery (see below)
When applying the above modifications to the original query, this is what I came up with:
FOR a IN X
LET relation = a.relation
FILTER LENGTH(relation) > 0
LET s = (
FOR r IN relation
FILTER r.field.v1 > 0
LIMIT 1
RETURN 1
)
FILTER LENGTH(s) > 0
RETURN a
As I said this probably won't improve performance greatly, however, you may get a different (potentially the desired) result from the query, i.e. less documents if multiple v1 in a document satisfy the filter condition.
Regarding indexes: fulltext and hash indexes will not help here as they support only equality comparisons, but the query's filter conditions is a greater than. The only index type that could be beneficial here in general would be the skiplist index. However, indexing array values is not supported in 2.7 at all, so indexing relation[*].field won't help and still no index will be used as you reported.
ArangoDB 2.8 will be the first version that supports indexing individual array values, and there you could create an index on relation[*].field.v1.
Still the query in 2.8 won't use that index because the array indexes are only used for the IN comparison operator. They cannot be used with a > as in the query. Additionally, when writing the filter condition as FILTER r[*].field.v1 > 0, this would evaluate to FILTER [null, 0, 0] > 0 for the example document above, which will not produce the desired results.
What could help here is a comparison operator modificator (working title) that could tell the operators <, <=, >, >=, ==, != to run the comparison on all members of its left operand. There could be ALL and ANY modifications, so that the filter condition could be written as simply FILTER a.relation[*].field.v1 ANY > 0. But please note that this is not an existing feature yet, but only my quick draft for how this could be fixed in the future.
