News for hackers. Stuff that matters.

A comment on Recursive SQL Queries with PostgreSQL. Parent comment

Re:Recursive SQL Queries with PostgreSQL by Izkata on Monday March 16, 2020 @08:27AM (#22592060)
Definitely measure the performance with sqlite though. I recently found one of my recursive queries had really bad performance compared with using a temporary table, because the CTE didn't create an automatic covering index. Had to separate it out into two queries (which changed it from something like .3s to .009s + .001s).
[ Parent | on HN ]
Re:Recursive SQL Queries with PostgreSQL by SQLite on Monday March 16, 2020 @09:03AM (#22592290)
If you will kindly send your problem query to the SQLite developers, we will do what we can to improve the query planner to make it run faster.
[ Parent | on HN ]
Re:Recursive SQL Queries with PostgreSQL by Izkata on Monday March 16, 2020 @11:55PM (#22601493)
Posted (and reran the numbers with some unnecessary transformations removed): https://sqlite.org/forum/forumpost/e4091b433a

I may have misremembered since I did go a bit wild on this query (done while learning about recursive queries), but pulling the subquery into a CTE doesn't affect the query plan.

[ Parent | on HN ]