Thanks for the answer. While this does indeed avoid materialising the CTE entirely up front, based on my reading of the VDBE trace it seems quite a bit less efficient. If I'm interpreting the traces correctly, each recursive step gets executed in its entirety. That means that, in this example, all children with the parentId 55115879 (which is the parent id of the row with id 60363923) are first added to the ephemeral table. Once that's done then the `cid > 60363923` clause is evaluated against the first row of the ephemeral table. Then the recursion step is executed with this row which ends up selecting the same rows as the first recursion step since the the parentId is identical. And so on... If the result is not found in the first parent, then I think this risks recursing infinitely. Solveable by using `union` instead of `union all`, but that will probably only add more overhead. It remained puzzling to me why the query planner would materialise a CTE that's the outermost loop in the join, is used once in the query, and is side effect free. To make sure the loops were being nested the way I expected them to be, I tried forcing the order using `CROSS JOIN` and that produces exactly the VDBE trace I was hoping to get. That leaves me with the question why the query planner decided to use the for loop nesting order it did. Is there any way to get debug/trace output for the decisions the query planner makes when choosing between various options? Perhaps not the most useful metric, but just FYI the vdbe traces give me these results: - original query: 102 opcodes - suggested alternative: 2310 opcodes - original with cross join: 42 opcodes