Performance when not using build tag "libsqlite3" about go-sqlite3 HOT 2 CLOSED

I wanted to follow up to let you know that I believe this is a sqlite issue and not something specific to go-sqlite3. After discovering that the "explain query plan" differed between the two tests, with the built-in version omitting a "USING AUTOMATIC COVERING INDEX", I downloaded and built sqlite3 v3.42.0 and reproduced the slow behavior there as well. I plan to spend some more time tomorrow going through the changelogs for sqlite to see if I can figure out what version introduced the change. Note: 3.40.1 is fine, as is 3.44.0 prerelease, so it seems to be a regression somewhere in there. Thanks for go-sqlite3!

from go-sqlite3.

Just for anyone who happens to stumble upon this post. Below are the commits that introduced the performance regression into sqlite and corrected it.

Introduced by:

commit 7d5a5491215eb48acba22a81067aae2661bb2fd6
Author: drh <>
Date: Thu Mar 16 10:17:30 2023 +0000

Do not use the one-pass optimization on an UPDATE if there is a subquery
in the WHERE clause, since if the subquery is hidden behind a short-circuit
operator, the subquery might not be evaluated until after one or more rows
have been updated.  Fix for the problem reported by
[forum:/forumpost/0007d1fdb1|forum post 0007d1fdb1].  This is the same
problem that was fixed by [73f0036f045bf371] only for UPDATE instead of
DELETE.

FossilOrigin-Name: 2c56b984a0bd3be5ec326a2109ea7b8f1d4ef63c8fc325caac9663cf2479eaff

Fixed by:

commit d192b4441de853d23fd377244dfcfbaa5b246ba7
Author: drh <>
Date: Tue Aug 1 00:34:03 2023 +0000

Restore part of the UPDATE one-pass optimization that was removed by
check-in [2c56b984a0bd3be5]: only disable one-pass if the WHERE clause
contains a subquery.  Allow subqueries in the SET expressions.
Fix for performance problem reported by
[forum:/forumpost/8ab195fd44e75ed0|forum  post 8ab195fd44e75ed0].

FossilOrigin-Name: 42916af9fc0f379a608a08db894400bd735a28e26ab1ffd604d1fddfbdb3ec0c

from go-sqlite3.