Comment by summarity | Hacker News Reader

Comment by summarity | Hacker News Reader

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

[2] https://sqlite.org/forum/info/96b9e5709cf47cda

So I just did the bisect and I narrowed it down to this change:

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, 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.

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.

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?

I wonder if this benchmark would have indicated a regression if it had been maintained past v3.26

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

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.

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.

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.

> 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

Reply on news.ycombinator.com