The 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.
It 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.
