[GitHub] [age] humzakt commented on issue #690: Index advice for hop queries

["humzakt (via GitHub)" <gi...@apache.org>]

humzakt commented on issue #690:
URL: https://github.com/apache/age/issues/690#issuecomment-1528975732

   Indexing in Apache AGE can help improve the performance of your queries, similar to how indexing works in Neo4j. Since Apache AGE is built on top of PostgreSQL, you can leverage PostgreSQL's native indexing capabilities to create indices on the properties of your vertices.
   
   To speed up your queries, you can create indices on the value property for each of the node types (typeTwo to typeSix). Here's how you can create indices for each node type:
   
   ```
   CREATE INDEX type_two_value_idx ON ag_vertex(typeTwo) USING btree (properties->>'value');
   CREATE INDEX type_three_value_idx ON ag_vertex(typeThree) USING btree (properties->>'value');
   CREATE INDEX type_four_value_idx ON ag_vertex(typeFour) USING btree (properties->>'value');
   CREATE INDEX type_five_value_idx ON ag_vertex(typeFive) USING btree (properties->>'value');
   CREATE INDEX type_six_value_idx ON ag_vertex(typeSix) USING btree (properties->>'value');
   ```
   
   These indices will improve the performance of your queries when searching for nodes based on the value property.
   
   However, it's worth noting that using a variable-length path pattern with a large maximum depth (e.g., [:REL*..100]) can still lead to slow query performance, as it requires traversing a significant portion of the graph. If possible, try to reduce the maximum depth to a smaller value based on your specific use case.
   
   In addition, you can optimize your queries by merging them into a single query using the UNION operator, which can help reduce the overall query execution time:
   
   ```
   MATCH (:typeTwo{value:"searchingValue"}) - [:REL*..100]- (node:typeOne) RETURN node.prop
   UNION
   MATCH (:typeThree{value:"searchingValue2"}) - [:REL*..100]- (node:typeOne) RETURN node.prop
   UNION
   MATCH (:typeFour{value:"searchingValue3"}) - [:REL*..100]- (node:typeOne) RETURN node.prop
   UNION
   MATCH (:typeFive{value:"searchingValue4"}) - [:REL*..100]- (node:typeOne) RETURN node.prop
   UNION
   MATCH (:typeSix{value:"searchingValue5"}) - [:REL*..100]- (node:typeOne) RETURN node.prop;
   
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org