My favourite random sqlite story: The company I once worked for used an outdated version of sqlite (3.8.6) in one of their products. The databases used got bigger and bigger and in a very big project one of the "already known to be slow"-queries took more than an hour on my laptop making the tool unusable.

On a quiet day, I was able to save the temporary table used as part of the process and run the problematic query against it in an isolated fashion.

The query returned an extremely high number of results and when I discovered this I questioned my SQL-fu, my sanity and my trust in computers.

I found that we were hit by a bug that was fixed 6 years before I discovered it (https://sqlite.org/src/info/6f2222d550f5b0ee7ed). Sqlite's query planner assumed that a field with a not null constraint can never be null, which isn't the case for the right hand table in a left join.

I fixed it by adding a not null check in the query and then later by updating the library. After that the 1 hour query ran in ~700 ms.

This faster run time also helped with smaller projects and in the end allowed extending our test suite considerably.

Tldr: Keep your dependencies up to date.