-
Notifications
You must be signed in to change notification settings - Fork 663
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add LTREE Path in Groups #1980
Comments
Using LTREE to fetch groups as compared to using Recursion offers better performance. I used retrieve all parents as an example, retrieving up to level 20 of the groups and here are the results. Jaeger ResultsOperation: Retrieve parents
SQL ExplainInsert into Groups:
Get Ancestors:
Get Parents Count:
Get Children:
Get Children Count:
Hyperfine Statistical MethodThe operations below were carried out on 50 groups.
|
Can you send the branch you used for testing? |
It is on this PR: #2223 Comparison is made with main. |
Additional remarks on this: Using LTREE has a limitation since the trigger that creates the path in the db causes a significant use of the stack memory. This implies that creating more than 50 groups in a single family tree, i.e., descendants up to the 200th + level, which is possible with the current Recursive implementation. |
We should have Postgres LTREE for groups https://www.postgresql.org/docs/current/ltree.html, which can use to query the group in hierarchy.
Implmentation
path
and type as LTREEpath
column should have value ofid
path
column should haveparent path
+.
+id
nlevel
to retrieve withlevel
(depth) and let the maxlevel
be 5or 10Ideas / Approaches :
By default we can show level 1 with pagination limit and offset. Stop support for view multiple level in retrieve. ( For this we already have get parent and children endpoints)subpath
to change parent. https://www.postgresql.org/docs/current/ltree.html#LTREE-FUNC-TABLE https://patshaughnessy.net/2017/12/14/manipulating-trees-using-sql-and-the-postgres-ltree-extensionExample Group table
Above table in SQL
Get Parents (Ancestors)
or
Output
Get Parents Count
or
Output
Get Children (Descendants)
or
Get Children count
or
The text was updated successfully, but these errors were encountered: