5 ms·
Fun fact: Recursive CTEs are one of those things where, if you come in relatively new to them, would make you go "who would ever want to use this, and why?!" F
by shrikant 8y ago
Fun fact: Recursive CTEs are one of those things where, if you come in relatively new to them, would make you go "who would ever want to use this, and why?!"
Funner fact: Once you come across a problem to which the solution is to use a recursive CTE (handling train movement graphs in an RDBMS? yes please!), you'll find yourself having to explain your reasoning for it in a manner that will let you truly grok recursive CTEs.
Funnest fact: Both the above will almost certainly happen more than once in your data-wrangling lifetime.
(Or maybe that was just me...)
- ccozan 8y agoWe are using this kind of CTEs for handling BoM trees. Everyone thinks we habe some complicated software that generates this tree. Well, we just maintain the correct parent-child relation. Rest is trivial, with recursive CTE.
- esaym 8y ago>BoM trees Bill of material trees? Could you maybe elaborate more?
- ccozan 8y agoYes, Bill of Material. On our business we have sub-sub...-sub components, so we need to keep it like a tree, but manage it in a RMDBS.
- dliff 8y agoVery cool, this was actually my first real-world use case for Postgres CTE's. (BOM part of homemade ERP software I built for my last company). Worked great!
- spacemanmatt 8y agoI thought I was crazy for implementing a dependency resolver in CTE but eventually the performance and database-encapsulation were serious winners.
- Amezarak 8y agoOne common use case I've found for recursive CTEs is generating tables of dates. Very frequently, I have a question along the lines of "how many x have occurred per day/week/month?", where x has a timestamp column, where the data could be sparse. One neat way to do this is to create a recursive CTE that begins by selecting the first relevant date, then unions that with the period (day/week/month) plus one up to the last date you're interested in. Once done, you can left join your date CTE against a COUNT aggregation grouped by the period you care about on your data table.
- blattimwind 8y agoWhy not just GROUP BY to_char(timestamp, some_format) / extract(something from timestamp)?
- dpcx 8y agoI assume because that doesn't give the 0 results in the query. The RCTE would.
- blattimwind 8y agoAh right. generate_series() might be another, possibly simpler way, to generate a time-series independent of what data in the relation actually exists and then join and group that series to the data relation.
- barrkel 8y ago> One common use case I've found for recursive CTEs is generating tables of dates. If you don't have table-valued functions, just create a table with all the dates for the next hundred years or so. The number of distinct dates is pretty manageable. This is the approach I use when calculating stats data via MySQL.
- shrikant 8y agoI'd second this for dates. Every data warehouse I've been involved in building has always had a date table for ~100 years depending on the industry use case.
- barrkel 8y agoMy order of discovery was more like this: 1) Recursive CTEs: that's nifty; I'll remember that for when I need it. 2) Here's a problem that involves chasing a linked list of rows, I know, I'll use a recursive CTE. 3) Oh dear, that doesn't perform well at all, I'm much better off denormalizing my lists into a separate column and using LIKE expressions, or something similar instead.
- nerdponx 8y agoDo some database systems have support for optimizing recursive queries?
- matthewaveryusa 8y agoThe best you can do is slap an index -- at the end of the day, as the parent said, you're traversing a linked list in random order which means that if your database index doesn't fit in RAM you'll be doing a random disk access per recursive step. Slow, but no slower than any other solution with that same problem. If you want to optimize such a query, go back to the drawing board and see if your data truly warrants a recursive random walk.
- zamalek 8y agoIt depends on the nature of the data. If it's hierarchical you can use ORDPATH[1], which results in an index scan. You can use ORDPATH to further solve other queries with a bit of creativity (such as this ancestor/descendant querying structure for directed cyclic graphs[2] that we needed). My spidey senses lead me to believe that you won't get far with optimizing CTEs in the query engine, especially if the backing temp table becomes too large for memory. [1]: http://www.dbis.informatik.hu-berlin.de/fileadmin/lectures/WS2011_12/NeueKonzepte_Vorlesung/ordpath.pdf http://www.dbis.informatik.hu-berlin.de/fileadmin/lectures/W... [2]: https://github.com/k2workflow/Clay/blob/master/src/SourceCode.Clay.Algorithms/Graph.md https://github.com/k2workflow/Clay/blob/master/src/SourceCod...