Hello, Thanks for the previous advice. We are reporting two interesting cases with different bisecting result using "sql-perf-fuzz".
Here are the steps for reproducing our observations. All steps are same except for the link for downloading new test-cases: [Our test environment] * Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux" * Database: TPC-C benchmark [Setup Test Environment] 1. build SQLite 3.27.2 (verion of Feb 2019) $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip $ unzip sqlite-src-3270200.zip $ mv sqlite-src-3270200 sqlite327 $ cd sqlite327 $ ./configure $ make $ cd .. 2. build SQLite 3.23.0 (verion of Apr 2018) $ wget https://www.sqlite.org/2018/sqlite-src-3230000.zip $ unzip sqlite-src-3230000.zip $ mv sqlite-src-3230000 sqlite323 $ cd sqlite323 $ ./configure $ make $ cd .. 3. download tpc-c for sqlite3 (scale-factor of 1) $ mkdir testcase $ cd testcase $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz $ tar xzvf tpcc_sqlite.tar.gz ; download regression queries $ wget https://gts3.org/~/jjung/sqlite/report2.tar.gz $ tar xzvf report1.tar.gz $ cd .. 4. launch two SQLites - start $ sqlite327/sqlite3 testcase/test.db $ sqlite323/sqlite3 testcase/test.db - for each DB, set up timer sqlite> .timer on - copy and paste extracted queries Here’s the time taken to execute four SQL queries on old (v3.23) and newer version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to know which commit activate the regression. +----------------------+--------+ | Query | Time | +----------------------+--------+ | 6.sql (v3.23) | 3 | | 6.sql (v3.27.2) | 156 | +----------------------+--------+ | 19.sql (v3.23) | 720 | | 19.sql (v3.27.2) | 1747 | +----------------------+--------+ 1) 6.sql shows x52 slow query execution. - bisect fossil commit: === 2018-07-26 === [57eb2abd5b] Generalize the constant propagation optimization so that it applies on every WHERE close, not just those that contain a subquery. This then demonstrates that the current implementation is inadequate since it does not take into account collating sequences. (user: drh tags: propagate-const-opt) 2) 19.sql shows x2.5 slow query execution - bisect fossil commit: === 2018-08-04 === [7d9072b027] Further logic simplifications that flow out of the omission of the column cache. (user: drh tags: omit-column-cache) Thanks for your support. Best regards, Jinho Jung _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
