search

bitnine-oss / agensgraph

AgensGraph, a transactional graph database based on PostgreSQL
http://www.agensgraph.org
Other
1.32k stars 146 forks source link

Nested Loop VLE - performance considerations #574

Open adguzun opened 1 year ago

adguzun commented 1 year ago

Hello!

We are in process of testing our prod-like data model on AgensGraph, simplified it looks like :

1k Elements connected to a Set which in it's turn connected to a Model, there are 1k Sets, so overall we have 1 million Elements connected to a Model via Sets(please see https://github.com/adguzun/diag-data/blob/main/diag.JPG). Each Element has parameter named model_id.

Some of our business rules require analysing parameter(s) of all Elements in a Model like in query below:

match(e:element) -[:is_in*..2]->(m:model{id:1}) where e.model_id =1 return count(e)

The problem with the query is that it uses Nested Loop VLE operation and scans Edge through index in a loop and number of loops depends on number of returned rows from parent operation. For queries returning relatively small number of rows performance is good, but for queries like in the example it degrades.(please see https://github.com/adguzun/diag-data/blob/main/plan.JPG) Nested Loop VLE operation repeated 1kk times 0.003 ms each - 3 sec overall.

Are there any Graph-specific tuning methods that could help here? I read through AgensGraph documentation but didn't managed to find info on how Graph data is scanned or any avaliable parameters to control that. Thanks!

emotionbug commented 1 year ago

we will looking into it. thanks for your reporting.