[GitHub] [incubator-age] pdpotter commented on issue #45: unique properties / indexing

[GitBox <gi...@apache.org>]

pdpotter commented on issue #45:
URL: https://github.com/apache/incubator-age/issues/45#issuecomment-812524846


   It would indeed be very useful to enable the creation of constraints / indexes. This could help to accelerate the creation of edges between nodes that were added earlier, which slows down a lot when there are a lot of nodes with the same label:
   ```
   SELECT * FROM cypher('graph_name', $$
   MATCH (d:LabelA {id: 1}), (r:LabelB {id: 2})
   CREATE (d)-[:Relation {prop: 'value'}]->(r)
   $$) as (a agtype)
   ```
   
   The `CREATE UNIQUE`, `CREATE CONSTRAINT`, and `CREATE INDEX` clauses / commands [are part of cypher, but not of openCypher](https://github.com/opencypher/openCypher/blob/master/docs/standardisation-scope.adoc#language-features-excluded-from-opencypher), so I don't know what the best approach would be here.
   
   @ehsanonline 
   You can create a unique index, which will prevent the creation of duplicate values, but will not speed up matching, by creating an immutable function that casts to json over text
   ```
   CREATE OR REPLACE FUNCTION get_name(properties agtype) 
     RETURNS text
   AS
   $BODY$
       select $1::text::json->>'name';
   $BODY$
   LANGUAGE sql
   IMMUTABLE;
   ```
   and using that immutable function in the create index command
   ```
   CREATE UNIQUE INDEX person_name_idx ON mygraph.person(get_name(properties)) ;
   ```


-- 
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.

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