[GitHub] [age] avowkind commented on issue #1240: Detach Delete performance questions

[via GitHub]

avowkind commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1730514697

   Some tools for diagnosis.
   
   
[observations_generator.py.txt](https://github.com/apache/age/files/12693958/observations_generator.py.txt)
   
   The attached python script will generate a test graph with mock data to 
illustrate the above schema. it outputs two sql files 
       - create_graph.sql: creates a graph with 1000 features, 100 properties, 
and 10 datasets
       - create_observations.sql: creates 1000 batches of 100 observations each
   
   create observations take about 3 minutes to create 1M observations.
   
   These queries list and count observations 
   ```sql
   LOAD 'age';
   
   -- list some observations in a dataset
   SET search_path = ag_catalog, "$user", public;
   SELECT * FROM cypher('perf_test_graph', $$
     MATCH (d:Dataset {id: 'dataset:1'})<-[:in]-(o:Observation)
     RETURN o
   $$) as (o agtype);
   
   This is fast - 100ms to return >10000 results.
   
   -- count obs in each dataset. 
   SELECT * FROM cypher('perf_test_graph', $$
     MATCH (d:Dataset)<-[:in]-(o:Observation)
     RETURN d.id, count(o) as observation_count
   $$) as (d_id text, observation_count bigint);
   ```
   This is also fast:  but noting that here if I omit the arrow on the edge <-  
then the query takes forever. 
   
   Code to delete observations
   ```sql
   LOAD 'age';
   SET search_path = ag_catalog, "$user", public;
   SELECT * FROM cypher('perf_test_graph', $$
     MATCH (d:Dataset {id: 'dataset:1'})<-[:in]-(o:Observation)
     DETACH delete o
     RETURN o
   $$) as (o agtype);
   ```
   
   This is slow.  Taking 1:46 mins. 
   
   


-- 
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-unsubscr...@age.apache.org

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