FS#79249 - [sqlite] Compile with SQLITE_ENABLE_STAT4

Attached to Project: Arch Linux
Opened by mille337 (mille337) - Sunday, 30 July 2023, 10:37 GMT
Last edited by Buggy McBugFace (bugbot) - Saturday, 25 November 2023, 20:23 GMT
Task Type Feature Request
Category Packages: Core
Status Closed
Assigned To Andreas Radke (AndyRTR)
Architecture All
Severity Low
Priority Normal
Reported Version
Due in Version Undecided
Due Date Undecided
Percent Complete 100%
Votes 0
Private No

Details

Description:
I noticed that one of my applications (FreshRSS) was much slower after running `ANALYZE` on its database (around 1GB). The reason is that the query planner turned out to pick a much worse query plan with `sqlite_stat1` than without. SQLite now have an improved `ANALYZE` logic that can work around such issues and should provide better plans overall but it's opt-in and needs to be turned on with the SQLITE_ENABLE_STAT4 compile option.

From my local tests, this seem to add about 13kB to the built library (although the compiler was not exactly the same).

Additional info:
* package version(s): 3.42.0
* SQLite doc: https://sqlite.org/compile.html#enable_stat4
* SO post explaining initial ANALYZE drawbacks: https://stackoverflow.com/a/26864482
   stat4.patch (0.4 KiB)
This task depends upon

Closed by  Buggy McBugFace (bugbot)
Saturday, 25 November 2023, 20:23 GMT
Reason for closing:  Moved
Additional comments about closing:  https://gitlab.archlinux.org/archlinux/p ackaging/packages/sqlite/issues/1
Comment by Toolybird (Toolybird) - Sunday, 30 July 2023, 21:12 GMT
FWIW, I don't see other distros enabling this.
Comment by mille337 (mille337) - Monday, 31 July 2023, 08:18 GMT
Yeah, that's a good point, from what I can tell Debian doesn't enable it. Not sure what is the reason, I can't find any bug reports asking to enable or disable it. I assume the reason is that it is a pretty invisible "feature" that doesn't change anything user-facing at the first glance.
Comment by Andreas Radke (AndyRTR) - Tuesday, 22 August 2023, 20:57 GMT
What do you think about this note "The downside of this compile-time option is that it violates the query planner stability guarantee making it more difficult to ensure consistent performance in mass-produced applications."

see also https://sqlite.org/queryplanner-ng.html#qpstab

I guess for common Arch (non-enterprise) use cases we can give STAT4 a higher priority over "query planner stability guarantee" and implement it.

Loading...