SQLite Forum

sqlite 3.41: major performance regression in views selecting from a TEXT column and a literal

sqlite 3.41: major performance regression in views selecting from a TEXT column and a literal

(1) By Even Rouault (rouault) on 2023-02-22 14:21:12 [source]

Given proj.db and test2.sql, there's a major performance regression between 3.40.0 and 3.41.0

$ hyperfine --warmup 10 "cat test2.sql|/home/even/install-sqlite-3.40.0/bin/sqlite3 proj.db"
  Time (mean ± σ):      10.4 ms ±   2.1 ms    [User: 9.3 ms, System: 2.4 ms]
  Range (min … max):     8.4 ms …  15.7 ms

$ hyperfine --warmup 10 "cat test2.sql|/home/even/install-sqlite-3.41.0/bin/sqlite3 proj.db"
  Time (mean ± σ):     113.6 ms ±  11.7 ms    [User: 109.6 ms, System: 4.7 ms]
  Range (min … max):    99.2 ms … 126.9 ms

test2.sql content is the following:

    SELECT auth_name, code, type FROM geodetic_crs
    SELECT auth_name, code, 'projected' FROM projected_crs;
SELECT type FROM VV WHERE auth_name = 'EPSG' AND code = '4326';
-- above SELECT repeated 100 times

If changing 'projected' to CAST('projected' AS TEXT), the performance regression disappears.

I've bisected this to commit 23f61a4ba80695ee6e9d88e56294d0dae24d5a41:

Author: drh <>
Date:   Fri Nov 25 15:52:00 2022 +0000

    Add restriction (9) to the push-down optimization:  If the subquery is
    a compound then all arms of the compound must have the same affinity.
    dbsqlfuzz 3a548de406a50e896c1bf7142692d35d339d697f.
    FossilOrigin-Name: 1ad41840c5e0fa702ba2c0df77a3ea126bd695b910b5d1271fa3129c38c58f5f

I've also included in test.sql, a more representative workload where the performance regression is about x1.7:

$ hyperfine --warmup 10 "cat test.sql|/home/even/install-sqlite-3.40.0/bin/sqlite3 proj.db"
  Time (mean ± σ):      10.8 ms ±   1.3 ms    [User: 8.9 ms, System: 2.9 ms]
  Range (min … max):     9.2 ms …  15.1 ms
$ hyperfine --warmup 10 "cat test.sql|/home/even/install-sqlite-3.41.0/bin/sqlite3 proj.db"
  Time (mean ± σ):      17.7 ms ±   0.5 ms    [User: 15.7 ms, System: 3.0 ms]
  Range (min … max):    16.9 ms …  20.1 ms

When run on test "proj_test_cpp_api" regression test of the PROJ library which includes thousands of queries similar to test.sql, the runtime goes from 18 seconds with SQLite 3.40, to 39 seconds with 3.41

(2.1) By Richard Hipp (drh) on 2023-03-23 15:31:04 edited from 2.0 in reply to 1 [link] [source]

I apparently neglected to say that this issue has been resolved by check-in aa6bd6dff751223e on 2023-02-23. The change is available on the latest trunk version of SQLite. See the README file on the source repository for additional instructions on how to download and compile the latest check-ins of SQLite. The fix will appear in the 3.42.0 release. It is not planned for any patch releases because it is only a performance issue and not a malfunction.

The same problem was also raised by a subsequent format post 3a180ba0d4.