Sure. Though hybrids are possible, e.g. encoding a fixed number of levels of recursion, with common prefixes held in or near the root. The main point I'm makin

Sure. Though hybrids are possible, e.g. encoding a fixed number of levels of recursion, with common prefixes held in or near the root. The main point I'm making is that you can grab a whole subtree on a prefix match right out of a single index, whereas recursive queries need new lookups at every step. Recursive queries look cool for someone with an object-oriented mental model - hey, look, you can make the database chase pointers - but chasing pointers is still slow, whether it happens in the database or in memory. (This was past me. I thought recursive queries looked neat for fetching tree-shaped data. So I built the data model and loaded it up, and then discovered that performance was absolute rubbish.)