avowkind opened a new issue, #1240:
URL: https://github.com/apache/age/issues/1240
I'm finding DETACH DELETE of a filtered list of nodes to be very slow in
comparison with the equivalent match or create. I understand there's work to do
but its taking 30 seconds to delete 1000 vertices.
Given a schema where we have Observations(100000s), Features (1000s) and
Properties( 100s) and Datasets (10s)
That each observation has three outgoing edges ( example values )
```
(o:Observation) -[observedProperty]-> (p:Property { id:
'water_temperature_celsius'})
(o:Observation) -[featureOfInterest]-> (f:Feature { id: 'tank:A01'})
(o:Observation) -[in]-> (d:Dataset { id: 'site_temperatures'})
```
The Observation has properties such as date, result etc.
Creating and Matching observations performance is very good - I can upload
1000s of observations a second and retrieving a set of observations filtered on
particular properties and belonging to a given dataset takes only a few seconds
to get MB of results.
However, Deleting observations is really really slow in comparison.
For example to remove all the obs in a dataset.
```
MATCH (o:Observation)-[in]->(d:Dataset { id: 'site_temperatures'} )
DETACH DELETE o
RETURN o
```
Takes so long it times out. I have seen examples where the database process
receives a kill 9 from the supervisor.
Adding LIMIT 1000 does complete but still takes several seconds.
Questions:
1 - am I doing this with the correct query?
2 - why might this take so long? - Although there are 1 vertex and 3 edges
to remove per observation this is not much more work to find than the
equivalent match.
I wonder whether:
- is this an indexing thing?
- Is this a lock / deadlock thing? - some suggestion that any other asynch
requests to the db to get observations ( which is quite likely) results in some
obs being 'in use'. blocking the delete. This might just be a side effect of
it taking so long.
This screen shot from PGAdmin taken at 14:06 shows a detach delete of all
observations that started at 13:26.
This is running on a MacBook Pro 2023. Apple M2 Max 64GB. MacOS Ventura.
PostgreSQL 15.
I see the same problem on an Ubuntu Linux server.
My app is running in docker - but I can reproduce the issue by putting the
same SQL request into PgAdmin.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
