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.
Comments
A follow up on this article was written today
[Recursion with PostgreSQL, Follup 1, Perfomances](https://github.com/vb-consulting/blog/discussions/4)
I managed to get some really good perfomances (757K records in 5 seconds).
Also, method 1 contained a nasty bug :(
That's why next time I'll try to focus on debugging and testing.
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.)
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.
https://www.postgresql.org/docs/current/queries-with.html#QU...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.