6 ms·

A follow up on this article was written today [Recursion with PostgreSQL, Follup 1, Perfomances](https://github.com/vb-consulting/blog/discussions/4 https://gi

• ttfkam 3y ago
  Wonderful! Just some possibilities for optimization of the recursive CTE. (I haven't tested it and don't know if it'd automatically improve things.) WITH RECURSIVE _recursive_cte AS MATERIALIZED ( … ) https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION https://www.postgresql.org/docs/current/queries-with.html#QU... Basically makes the temp table from your other implementation for you. Then you could use built-in cycle detection to be able to simply the syntax while also allowing UNION ALL instead of UNION for some extra speed. CYCLE id SET is_cycle USING path https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CYCLE https://www.postgresql.org/docs/current/queries-with.html#QU... > As far as I know, SQL was never even intended to do that in the first place. 49 years ago, certainly not. Postgres still can't beat a dedicated graph database for non-trivial hierarchies, but a lot of work has already been done in the last five years to get it a lot closer to that milestone.