What performance gain should I be expecting with indexes on graph databases?

[shasaur]

I'm trying to incorporate indexes into my ArcadeDB set-up. I want to know if the indexes I set-up are correctly configured and being utilised. I've set them up with:

create property Action.personId if not exists integer;
create property EntityAction.personId if not exists integer;
create index if not exists on Action (personId) notunique;
create index if not exists on EntityAction (personId) notunique;

Given a query which takes about ~5.5ms (range 5.0-6.5ms)

MATCH (act:Action {personId: 1})<-[:OF_ACTION]-(entityAct:EntityAction {personId: 1})
RETURN id(entityAct) AS id, act.name AS actionName

With node numbers:

• Action nodes: 1,474
• EntityAction nodes: 23,695

What new performance should I expect? There's about 70 personId values (categories you could say) for which the data is split evenly between. I'm seeing a new performance of ~4.5ms (range 3.6-5.2ms), is that to be expected? I think I would have expected faster for this use case of indexes.

Further information

Explain query without indexes:

Traversal Metrics
Step                                                               Count  Traversers       Time (ms)    % Dur
=============================================================================================================
ArcadeFilterByTypeStep(vertex,Action)                               1474        1474           0.526    10.26
HasStep([personId.eq(1)])@[act]                                       22          22           0.718    14.01
VertexStep(IN,[OF_ACTION],vertex)                                    356         356           0.277     5.41
HasStep([~label.eq(EntityAction), personId.eq(1...                   356         356           0.366     7.15
SelectStep(last,[entityAct, act])                                    356         356           0.251     4.89
NoOpBarrierStep(2500)                                                356         356           0.335     6.54
ProjectStep([id, actionName],[[SelectOneStep(la...                   356         356           2.655    51.74
  SelectOneStep(last,entityAct,null)                                 356         356           0.109
  IdStep                                                             356         356           0.093
  SelectOneStep(last,act,null)                                       356         356           0.117
  ChooseStep([PropertiesStep([name],value), Has...                   356         356           0.990
    PropertiesStep([name],value)                                     356         356           0.137
    HasNextStep                                                      356         356           0.068
    PropertiesStep([name],value)                                     356         356           0.104
    EndStep                                                          356         356           0.077
    ConstantStep(null)                                                                         0.000
    EndStep                                                                                    0.000
                                            >TOTAL                     -           -           5.131        -

Explain query with indexes:

Traversal Metrics
Step                                                               Count  Traversers       Time (ms)    % Dur
=============================================================================================================
ArcadeFilterByIndexStep(vertex,[com.arcadedb.da...                    22          22           0.041     0.95
HasStep([personId.eq(1)])@[act]                                       22          22           0.027     0.63
VertexStep(IN,[OF_ACTION],vertex)                                    356         356           0.274     6.29
HasStep([~label.eq(EntityAction), personId.eq(1...                   356         356           0.328     7.52
SelectStep(last,[entityAct, act])                                    356         356           0.231     5.31
NoOpBarrierStep(2500)                                                356         356           0.330     7.58
ProjectStep([id, actionName],[[SelectOneStep(la...                   356         356           3.129    71.72
  SelectOneStep(last,entityAct,null)                                 356         356           0.133
  IdStep                                                             356         356           0.105
  SelectOneStep(last,act,null)                                       356         356           0.130
  ChooseStep([PropertiesStep([name],value), Has...                   356         356           1.147
    PropertiesStep([name],value)                                     356         356           0.149
    HasNextStep                                                      356         356           0.080
    PropertiesStep([name],value)                                     356         356           0.124
    EndStep                                                          356         356           0.090
    ConstantStep(null)                                                                         0.000
    EndStep                                                                                    0.000
                                            >TOTAL                     -           -           4.362        -

[lvca]

The index doesn't make a lot of difference here, from your table it's only 0.95% of the total time, while the rest is Gremlin...

Are you using Gremlin via remote Java API or HTTP/Postgres?

If you run this SQL query, how fast is it? I'v use ChatGPT 4 to translate your query into OrientDB/ArcadeDB SQL:

---

Below is the converted ArcadeDB SQL query using the MATCH statement. Please note that ArcadeDB uses record IDs (rids) and aliases differently than Neo4j. ArcadeDB does not have an inherent id() function similar to Neo4j's; instead, it uses the @Rid field to retrieve the unique record ID. The property names must match the database schema in your ArcadeDB instance.

SELECT entityAct.@rid AS id, act.name AS actionName
FROM (
  MATCH 
    {class: Action, as: act, where: (personId = 1)}.in('OF_ACTION')
    {class: EntityAction, as: entityAct, where: (personId = 1)}
  RETURN act, entityAct
)

Please ensure that your class names (Action and EntityAction) and your property names (personId and name) match the schema defined in your OrientDB database. The edge class is assumed to be OF_ACTION in this example based on the Cypher query. The direction .in('OF_ACTION') specifies an incoming edge to the Action.

Adjust the class names, edge names, and property names as necessary to properly align with your actual database schema.

---

[shasaur]

I'm running the database as a server with the postgres plugin. The queries in this post have been made using the ArcadeDB web interface.

Using SQL mode to execute your query yields the following error: Cannot execute command Encountered <FROM> "FROM" at line 2, column 1. Was expecting one of: <EOF> <LIMIT> ... <SKIP2> ... <OFFSET> ... <TIMEOUT> ... <LET> ... <UNWIND> ... ";" ... "," ... "," ...

[lvca]

Is there any way I can have your database to run some tests? Even with fake data is fine, I just need the structure and some records

[shasaur]

Sure, I'll send it over a Discord private DM if that's okay.

[lvca]

Ok, that was my bad: I used the OrientDB syntax with class instead of type. This is the right query:

SELECT entityAct.@rid AS id, act.name AS actionName 
FROM (
  MATCH
    {type: Action, as: act, where: (personId = 1)}.in('OF_ACTION')
    {type: EntityAction, as: entityAct, where: (personId = 1)}
  RETURN act, entityAct
)

This is the profiling where the timing is in microseconds:

+ FETCH FROM SUBQUERY 
  + SET 
     act
    AS
    + FETCH FROM TYPE Action (142μs)
      + FETCH FROM BUCKET 169 (Action_0) ASC = 6 RECORDS (142μs)
      + FETCH FROM BUCKET 172 (Action_1) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 175 (Action_2) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 178 (Action_3) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 181 (Action_4) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 184 (Action_5) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 187 (Action_6) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 190 (Action_7) ASC = 0 RECORDS ()
    + FILTER ITEMS WHERE  (96μs)
      personId = 1
  + MATCH      ---->
    {act}.in('OF_ACTION'){entityAct}
  + CALCULATE PROJECTIONS (53μs)
    act, entityAct
+ LIMIT ( LIMIT 25)
+ CALCULATE PROJECTIONS (87μs)
  entityAct.@rid AS id, act.name AS actionName

[lvca]

It's consistently 8-10X faster than Cypher on my Mac.

[lvca]

When I remove the limit from studio I see the index performs worse because 8 buckets were used.

    + FETCH FROM TYPE Action (136μs)
      + FETCH FROM BUCKET 169 (Action_0) ASC = 36 RECORDS (19μs)
      + FETCH FROM BUCKET 172 (Action_1) ASC = 36 RECORDS (19μs)
      + FETCH FROM BUCKET 175 (Action_2) ASC = 36 RECORDS (18μs)
      + FETCH FROM BUCKET 178 (Action_3) ASC = 33 RECORDS (16μs)
      + FETCH FROM BUCKET 181 (Action_4) ASC = 31 RECORDS (16μs)
      + FETCH FROM BUCKET 184 (Action_5) ASC = 31 RECORDS (15μs)
      + FETCH FROM BUCKET 187 (Action_6) ASC = 31 RECORDS (15μs)
      + FETCH FROM BUCKET 190 (Action_7) ASC = 30 RECORDS (15μs)

This means there are 8 sub-indexes, one per bucket. With the default bucket selection strategy, the query has to execute 8 lookups. A way to make this faster is to create the types with only 1 bucket or use multiple buckets but with a partitioned strategy. In this way, the index used will be the partition selected with its ID and ArcadeDB will know in which bucket to lookup.

[shasaur]

Hmm, 're-write your codebase to SQL / OrientDB' was not the answer I was looking for.

Can you point me to the documentation for using only 1 bucket or the multiple partitioned buckets?

[lvca]

Check the CREATE VERTEX TYPE command: https://docs.arcadedb.com/#SQL-Create-Type. You can create it with CREATE VERTEX TYPE Action BUCKETS 1 or via Java API (pass 1 as the number of buckets).

Or if you want to use multiple buckets (suggested if you have millions of records and you need high insertion speed using multiple threads), look at: https://docs.arcadedb.com/#Bucket-Selection.

[lvca]

@shasaur what solution you end up using?