complex CTE query goes from 1.5s in 3.46.1 to 17s in 3.47.2

(1) By gotson on 2025-01-14 04:35:51 [link] [source]

Hi,

i recently had some reports of poor performance in my application, after some investigation i narrowed it down to the version of SQLite used.

I have a database.sqlite file provided by a client that is ~7GB.

The impacted query is using 2 CTE expressions.

In 3.46.1, the query takes ~1.5s.

In 3.47.2, the same query takes ~17s.

I have ran explain query plan on both versions, and i get a different plan, but i don't know what i can do from there.

I can always remain on 3.46.1 for now, but that won't work in the long term.

What steps could I take in order to try to resolve this ?

I could provide the database file (i would need to ask my user if that is OK to share), as well as the query, if that is something that could help to understand why the 3.47 version doesn't perform as well as 3.46.

(2.1) By Gunter Hick (gunter_hick) on 2025-01-14 09:30:23 edited from 2.0 in reply to 1 [link] [source]

You will greatly increase the rate of useful responses if you show your work. Like the schema and the actual statement.

There have recently been several threads where using MATERIALIZED has helped to find a better query plan.

(5) By gotson on 2025-01-15 06:36:35 in reply to 2.1 [link] [source]

Thanks, i tried MATERIALIZED and got similar timings as 3.46.

(3) By Richard Hipp (drh) on 2025-01-14 15:22:32 in reply to 1 [link] [source]

You can send your sample database and query to drh at sqlite dot org.

(6) By Richard Hipp (drh) on 2025-01-21 01:34:03 in reply to 3 [source]

Thanks for the data.

I think this performance issue has been resolved by check-in 0852c57ee2768224, which is also the fix for forum post d87570a145599033. But that might just be luck. I will continue to investigate.

I have noticed that the query runs in less than 1 second if I compile without -DSQLITE_DEBUG. But if I add -DSQLITE_SQLITE to the build, it takes more than 30 seconds. I need to understand that disparity better. I expect SQLITE_DEBUG to slow things down some, but not 30x.

(4) By Thomas Hess (luziferius) on 2025-01-14 17:31:12 in reply to 1 [link] [source]

Did you run ANALYZE after updating to 3.47?
You could try the .expert command. Open the db in the CLI, run .expert, then execute the query. If it suggests a new index, try creating it, run ANALYZE again, and see if that helped.
Try extracting parts of the query, and run them separately. You may be able to identify the slow part that way