Strange behaviour with vertex centric indexes

user_3545219 0
Viewed 36

I have some trouble understanding how to properly use vertex centric indexes in ArangoDB.

In my cooking app, I have the following graph schema : (recipe)-[hasConstituent]->(ingredient)

Let say I want all the recipes that need less than 0g of carrots. Result will be empty of course.

FOR recipe, constituent, p IN INBOUND 'ingredients/carrot' hasConstituent
    FILTER constituent.quantity.value < 0
    RETURN recipe._key

With carrot having 400.000 recipes associated, this query takes ~3.9s. Fine.

Now I create a vertex centric index in hasConstituent collection on _to,quantity.value properties, with an estimated selectivity of 100%.

I expected it to sort indexes in a numeric order, and then to significantly increase the speed of FILTER or SORT/LIMIT requests, but now the previous request takes ~7.9s... If I make the index "sparse", it takes the same time as without index (~3.9s)

What am I missing here ?

The most hard part to understand is that the execution plan given by the explain result is different from the profile result. Here is the explain, where all is fine and should fetch the result instantly :

Execution plan:
 Id   NodeType          Est.   Comment
  1   SingletonNode        1   * ROOT
  5   TraversalNode        1     - FOR recipe  /* vertex */, constituent  /* edge */ IN 1..1  /* min..maxPathDepth */ INBOUND 'ingredients/carrot' /* startnode */  hasConstituent
  6   CalculationNode      1       - LET #8 = (constituent.`quantity`.`value` < 0)   /* simple expression */
  7   FilterNode           1       - FILTER #8
  8   CalculationNode      1       - LET #10 = recipe.`_key`   /* attribute expression */
  9   ReturnNode           1       - RETURN #10

But in the profile :

Execution plan:
 Id   NodeType          Calls    Items   Runtime [s]   Comment
  1   SingletonNode         1        1       0.00000   * ROOT
  5   TraversalNode       433   432006       7.64893     - FOR recipe  /* vertex */, constituent  /* edge */ IN 1..1  /* min..maxPathDepth */ INBOUND 'ingredients/carrot' /* startnode */  hasConstituent
  6   CalculationNode     433   432006       0.28761       - LET #8 = (constituent.`quantity`.`value` < 0)   /* simple expression */
  7   FilterNode            1        0       0.08704       - FILTER #8
  8   CalculationNode       1        0       0.00000       - LET #10 = recipe.`_key`   /* attribute expression */
  9   ReturnNode            1        0       0.00001       - RETURN #10

I precise the index is used in both results :

Indexes used:
 By   Name              Type         Collection       Unique   Sparse   Selectivity   Fields                        Ranges
  5   recipeByIngrQty   persistent   hasConstituent   false    false       100.00 %   [ `_to`, `quantity.value` ]   base INBOUND

Any help is very welcome

arangodb
1 Answers

For a traversal FOR vertex, edge, path IN ..., filtering on either vertex or edge only applies to the results, but not what's actually visited. As to why that makes sense, keep in mind that generally, not all vertices or edges visited during the traversal are actually part of the result: For example, if min in the IN min..max argument is larger than zero - it's one by default - vertices (and their incoming edges) with distance lower than that are not part of the result, but have to be visited.

That's why, if you want to restrict the edges visited during a traversal, you must add the filter on the path variable instead. For your example:

FOR recipe, constituent, p IN INBOUND 'ingredients/carrot' hasConstituent
    FILTER p.edges[*].quantity.value ALL < 0
    RETURN recipe._key

That should make use of the index as you expected. See vertex centric indexes and the AQL graph traversal documentation for more details.

I think this answers the core of your question, now to clear up some of the ones you found on the way.

I expected it to sort indexes in a numeric order, and then to significantly increase the speed of FILTER or SORT/LIMIT requests, but now the previous request takes ~7.9s... If I make the index "sparse", it takes the same time as without index (~3.9s)

Two things here.

First, it sounds like the optimizer preferred your index over the edge index. That probably shouldn't be the case, as (without the change I described above) it's not more specific than the edge index, but just somewhat slower. You haven't specified the version of ArangoDB your using, so I can't comment specifically. But if you are using the most recent patch release of a supported minor version, e.g. 3.7.10 or 3.6.12 at the time of this writing, you can report this as an issue on Github.

Second, a sparse index does not index non-existent or null values. It thus cannot be used for a query that could report null values. Now note that null < 0 is true, see type and value order in the documentation for details. So your query constituent.quantity.value < 0 could report null values, and that's why the sparse index is treated differently (i.e. cannot be used at all).

Now to the final point:

The most hard part to understand is that the execution plan given by the explain result is different from the profile result.

The explain output shows a column "Est.", which is an estimate of the number of rows / iterations this node will emit / do. The column "Items" in the profile output in contrast is the corresponding exact number. Now this estimate can be good in some cases, but bad in others. This is not necessarily a problem, and it cannot be exact without actually executing the query. If it happens to result in a problem, because the estimates get the optimizer to choose the wrong index for the job, you can use index hints. But this isn't the case here.

Apart from that, the two plans you showed seem to be exactly identical.

user_19742570
Related