6.8.2.3 Performance Considerations for PGQL Queries

The following are some recommended practices for query performance.

Recursive Queries

The following indexes are recommended in order to speed up execution of recursive queries:

• For underlying VERTEX tables of the recursive pattern, an index on the key column
• For underlying EDGE tables of the recursive pattern, an index on the source key column

  Note:

  You can also create index on (source key, destination key).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (person1, person2)
      SOURCE KEY ( person1 ) REFERENCES person
      DESTINATION KEY ( person2 ) REFERENCES person
      NO PROPERTIES
  )
  OPTIONS ( PG_VIEW )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

• CREATE INDEX <INDEX_NAME> ON PERSON(ID)
• CREATE INDEX <INDEX_NAME> ON KNOWS(PERSON1) or

  CREATE INDEX <INDEX_NAME> ON KNOWS(PERSON1, PERSON2)

Composite Vertex Keys

For composite vertex keys, query execution can be optimized with the creation of function-base indexes on the key columns:

• For underlying VERTEX tables of the recursive pattern, a function-based index on the comma-separated concatenation of key columns
• For underlying EDGE tables of the recursive pattern, a function-based index on the comma-separated concatenation of source key columns

  Note:

  You can also create index on (source key columns, destination key columns).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id1, id2 )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (id)
      SOURCE KEY ( id1person1, id2person1 ) REFERENCES person
      DESTINATION KEY ( id1person2, id2person2 ) REFERENCES person
      NO PROPERTIES
  )
  OPTIONS ( PG_VIEW )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

• CREATE INDEX <INDEX_NAME> ON PERSON (ID1 || ',' || ID2)
• CREATE INDEX <INDEX_NAME> ON KNOWS (ID1PERSON1 || ',' || ID2PERSON1) or

  CREATE INDEX <INDEX_NAME> ON KNOWS (ID1PERSON1 || ',' || ID2PERSON1, ID1PERSON2 || ',' || ID2PERSON2)

If some of the columns in a composite vertex key is a string column, the column needs to be comma-escaped in the function-base index creation.

For example, if column ID1 in table PERSON of the preceding example is of type VARCHAR2(10), you need to escape the comma for the column as follows:

replace(ID1, ',', '\,')

So, the indexes to improve performance will result as shown:

• CREATE INDEX <INDEX_NAME> ON PERSON (replace(ID1, ',', '\,') || ',' || ID2)
• CREATE INDEX <INDEX_NAME> ON KNOWS (replace(ID1PERSON1, ',', '\,') || ',' || ID2PERSON1)