I have seen a few posts on HN regarding recursive CTEs recently and I shy away from them because it seems like an additional invocation for each different recursive lookup and a lot of the queries can be rewritten and potentially optimized using more traditional approaches.
Where I have control of the design though, I still prefer to create structures that don't* require recursive queries*, even considering this means a little extra logic on insert/update/delete. Depending on circumstance (and for these examples assuming a tree or forest, not a more general graph) this structural difference is either storing the full ID path of each node or a matrix of "node Z is under node Y by one level, node Z is under node X by two levels, node Y is under node X by one level, ...), instead of (or as well as) the naive tree structure. Well written operations on these structures perform better than a recursive query, sometimes significantly so, but of course it is a trade-off as you have to accept the extra space (and potentially RAM) needed and extra processing on node changes, additions, deletes, & moves.
"Yes, you heard that right, the SQL statement is 5x more performant at the database level alone."
I will have to do more testing and see what I can accomplish.
On a different product now (using PG) and guess what has come up? We need tree nodes! My first thought: anything but CTEs. ltree, materialized paths, neo4j, anything but CTEs. They've just burned me so badly before I'm too afraid to try them in PG.
- Send one request for each level of hierarchy? Worst of all.
- Stored procedure? I don't think it would be optimised compared to a recursive query.
See also: http://stackoverflow.com/questions/5861272/postgresql-with-r...
http://stackoverflow.com/questions/935098/database-structure...