4 ms·
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
by ttfkam 3y ago
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.
- vbilopav 3y agoA follow up on this article was written today [Recursion with PostgreSQL, Follup 1, Perfomances](https://github.com/vb-consulting/blog/discussions/4 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.
- ttfkam 3y agoWonderful! 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.