A query that ran fine under SQLite 3.24 is substantially slower in 3.25: SQLite version 3.24.0 2018-06-04 19:24:41 > .timer on > select a.id from a join c on a.id = case when c.b_a_name is not null then c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where a_fts match 'r*' order by rank) group by a.id; Run Time: real 0.037 user 0.019868 sys 0.016376
SQLite version 3.25.0 2018-09-15 04:01:47 > .timer on > select a.id from a join c on a.id = case when c.b_a_name is not null then c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where a_fts match 'r*' order by rank) group by a.id; Run Time: real 4.525 user 2.055779 sys 2.466143 Performance in 3.25.1 and 3.25.2 is similar to 3.25.0. The bottleneck seems to be in the CASE portion of the query. I am not sure why. To try and make a valid comparison I compiled the versions identically using the following flags: % gcc shell.c sqlite3.c -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_DECLTYPE -DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_STAT4 -DHAVE_READLINE -DHAVE_USLEEP -lreadline -Os Here is the compiler info: Apple LLVM version 10.0.0 (clang-1000.11.45.2) Target: x86_64-apple-darwin18.2.0 Thread model: posix What could be causing this performance hit in 3.25? Thanks, Stephen _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
