4 ms·

This version is still buggy for large CTE or JOINs for edge cases. A bug has been introduced with the query planner optimizations in SQLite >3.39 (3.4X) that de

This version is still buggy for large CTE or JOINs for edge cases. A bug has been introduced with the query planner optimizations in SQLite >3.39 (3.4X) that degrades query performance in some cases predictably by 3 orders of magnitude. So just to see if this was still a problem, I recompiled findsight.ai with this new amalgamation and yep, all queries that used to take about 300ms now take 3 to 10 seconds to complete. There's no difference in the query plans created, it's just a bug. I'll probably bisect by commit (or whatever Fossil calls it) at some point to nail down what changed, but then again 3.39 is rock solid enough I don't have a reason to upgrade. In the meantime, if you're running into one of these edge cases, try downgrading to 3.39. I also have no gripes, it clearly works well enough for many people (since by now most bundles and bindings are running 3.4X). Variants[1][2] of this bug have been reported, and some apparently fixed, but this continues to be an issue. [1] https://sqlite.org/forum/forumpost/bcc4375032 https://sqlite.org/forum/forumpost/bcc4375032 [2] https://sqlite.org/forum/info/96b9e5709cf47cda https://sqlite.org/forum/info/96b9e5709cf47cda

kccqzy 3y ago
While I sympathize with you, in both links the maintainer said it had been fixed. Maybe you ought to report a new bug? I didn't see any evidence regarding "maintainers consider[ing] this not a bug worth porting back fixes for because it still gives correct results" in your links.
- summarity 3y ago
  No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". This is mostly a hint to people that may run into similar issues while upgrading. And my reference concerned this comment (which I may have misunderstood, sorry if that's the case): > It is not planned for any patch releases because it is only a performance issue and not a malfunction.
  - TheRealPomax 3y ago
    patch releases are the third number (e.g. the "1" in "3.43.1"). The comment says nothing about it not qualifying as work to be done for a minor release (e.g. the "43" in "3.43"). If the bug is closed, and you still see it happening, file a new bug with a reproducible example so that the maintainers know it's (still) happening. And if that example means setting up a small dedicate repo with enough data to show the problem off, that's probably worth doing.
  - nextaccountic 3y ago
    > No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". Well the sqlite devs should be aware of your specific test case (the specific query that is still 3 orders of magnitude slower). Are they? After this the ball is with them
summarity 3y ago
So I just did the bisect and I narrowed it down to this change: https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941ab23ce041313d359f6788c4dde6b1ca749ab49137 https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941a... This seems to have had a wider fallout, from there (thanks Fossil, cool feature!) I found the reference back to https://sqlite.org/forum/forumpost/64d36440e473516c https://sqlite.org/forum/forumpost/64d36440e473516c, which reports another similar regression. Simply patching 3.43 to remove this cost adjustment doesn't fix the regression though, so I'll keep digging. Edit: a workaround seems to be an ultra-agressive use of ANALYZE to give the autoindexing a better chance. Setting the analyze limit to -1 (unlimited) and forcing all pool connections to ANALYZE on close (i.e. not relying on PRAGMA optimize) does "fix" the regression the next time the service runs.
- alberth 3y ago
  I read through the forum thread, it's not clear to me - has a bug been reopened for this? Thanks for diving into this btw.
  - SQLite 3y ago
    No - because nobody has provided a reproducible test case to show an actual performance regression. If we can't reproduce it, then how are we suppose to fix it?
alberth 3y ago
I wonder if this benchmark would have indicated a regression if it had been maintained past v3.26 https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html Regardless, I’d suggest opening a reproducible bug. They are really responsive to those types of things. SQLite is one of the few product I know that only ships if there are no open bugs. https://www.sqlite.org/cgi/src/rptview/1 https://www.sqlite.org/cgi/src/rptview/1

kccqzy 3y ago

While I sympathize with you, in both links the maintainer said it had been fixed. Maybe you ought to report a new bug? I didn't see any evidence regarding "maintainers consider[ing] this not a bug worth porting back fixes for because it still gives correct results" in your links.

summarity 3y ago
No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". This is mostly a hint to people that may run into similar issues while upgrading. And my reference concerned this comment (which I may have misunderstood, sorry if that's the case): > It is not planned for any patch releases because it is only a performance issue and not a malfunction.
- TheRealPomax 3y ago
  patch releases are the third number (e.g. the "1" in "3.43.1"). The comment says nothing about it not qualifying as work to be done for a minor release (e.g. the "43" in "3.43"). If the bug is closed, and you still see it happening, file a new bug with a reproducible example so that the maintainers know it's (still) happening. And if that example means setting up a small dedicate repo with enough data to show the problem off, that's probably worth doing.
- nextaccountic 3y ago
  > No, you are correct, it's just hard to debug something and produce a minimal case when the only change is "performance bad". Well the sqlite devs should be aware of your specific test case (the specific query that is still 3 orders of magnitude slower). Are they? After this the ball is with them

summarity 3y ago

So I just did the bisect and I narrowed it down to this change: https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941ab23ce041313d359f6788c4dde6b1ca749ab49137 https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941a... This seems to have had a wider fallout, from there (thanks Fossil, cool feature!) I found the reference back to https://sqlite.org/forum/forumpost/64d36440e473516c https://sqlite.org/forum/forumpost/64d36440e473516c, which reports another similar regression. Simply patching 3.43 to remove this cost adjustment doesn't fix the regression though, so I'll keep digging. Edit: a workaround seems to be an ultra-agressive use of ANALYZE to give the autoindexing a better chance. Setting the analyze limit to -1 (unlimited) and forcing all pool connections to ANALYZE on close (i.e. not relying on PRAGMA optimize) does "fix" the regression the next time the service runs.

alberth 3y ago
I read through the forum thread, it's not clear to me - has a bug been reopened for this? Thanks for diving into this btw.
- SQLite 3y ago
  No - because nobody has provided a reproducible test case to show an actual performance regression. If we can't reproduce it, then how are we suppose to fix it?

alberth 3y ago

I wonder if this benchmark would have indicated a regression if it had been maintained past v3.26 https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html Regardless, I’d suggest opening a reproducible bug. They are really responsive to those types of things. SQLite is one of the few product I know that only ships if there are no open bugs. https://www.sqlite.org/cgi/src/rptview/1 https://www.sqlite.org/cgi/src/rptview/1