search

feincms / django-tree-queries

Adjacency-list trees for Django using recursive common table expressions. Supports PostgreSQL, sqlite, MySQL and MariaDB.
https://django-tree-queries.readthedocs.io/
BSD 3-Clause "New" or "Revised" License
427 stars 27 forks source link

Support filtered tree query #50

Closed elonzh closed 6 months ago

elonzh commented 1 year ago

Currently the CTE query build tree in table scope and this make a huge performance impact when table is big.

In my situation, we seprate the records by user id which means the performance overhead will be reduced if we can build tree at the user level.

matthiask commented 1 year ago

Maybe django-cte has a solution? https://github.com/dimagi/django-cte/blob/master/tests/test_recursive.py

I think there's some hope to avoid the optimization fence in PostgreSQL in the future and allow pushing down predicates into the CTE – avoiding this performance problem in the database engine itself isn't impossible but I think PostgreSQL still doesn't support it. I don't know about other database engines.

I'm not sure but I think that django-tree-queries isn't well suited when it comes to extending or customizing the CTE itself. The implementation of django-cte is probably a better fit when it comes to supporting more elaborate queries, I'm not sure.

matthiask commented 6 months ago

Fixed by #66