Good comparison but seems to be missing how fast they run. If they're about the same, the skill set and expectations of the devs tips the scales. However if one is noticeably faster than the other, especially on a frequently run query, devs need to adapt to understanding the faster variant despite their initial comfort level.
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
Good comparison but seems to be missing how fast they run. If they're about the same, the skill set and expectations of the devs tips the scales. However if one is noticeably faster than the other, especially on a frequently run query, devs need to adapt to understanding the faster variant despite their initial comfort level.
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.