> If the CTE were not materialized then the recursive query would run returning one row by each for inspection for the terminating condition which, if found, would terminate the entire statement processing (all branches, not just the one containing the desired condition). That's indeed precisely the intention. My original question was not really about query correctness. I was trying to figure out how I can get the engine to execute it as efficiently as possible. Some more background information might clarify things. In this particular database, the rows are inserted in bulk in a controlled order and the database is read-only after that. The entries are added in depth-first order where each entry is assigned an incrementing id on insertion. This has some interesting properties: - For each parent P of any entry E, P.id < E.id - For each disjoint subtree with root entries S1 and S2, if S1.id < S2.id then for each child C1 of S1 C1.id < S2.id. These properties let you perform nested set style queries on the dataset. The lower bound value for a subtree with root R is R.id itself. The query from my question is intended to find the upper bound value. It visits each parent P of R from deep to shallow and queries for the smallest id value of an entry E where E.parentId = P.id and E.id > R.id. If there is no such entry, R is itself (or is a child of) the last entry in P and you need to inspect the next parent. Once you have the lower and upper bounds, selecting an entire subtree can be done using `where id > lower and id < upper` without requiring recursion. Why do I want to do this? 99% of the queries I'll be doing are single level queries for the direct children or direct parent of an entry. The adjacency list is the most practical for those purposes. For those cases where an entire subtree needs to be queried, I can do a relatively cheap recursive query which lets me do the subtree query using a simple index scan instead of a recursive query.