nothing really helps with potato slow Android device / storage media. CTE is not magic sauce that will make sqlite go faster.

Depending on the recursion pattern and the overhead of your sqlite driver, it can be faster to do many ID lookups then try to cram it all into one mega CTE query.

https://www.sqlite.org/np1queryprob.html

source: we have the CTE for loading page data in the notion Android app, and the network regularly beats disk on lower end Android devices using whichever query we pick.