On Fri, Apr 24, 2015 at 7:56 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 25 Apr 2015, at 1:12am, John Pitney <john at pitney.org> wrote: > >> The results are the following, on a Windows 7 64-bit platform: > > Please do ANALYZE, then check your times again.
Inserting an ANALYZE just before the final SELECT statement caused run times for large M to increase by 40%. The query plan changes in a way that slows down this particular query. Here is what EXPLAIN QUERY PLAN shows for the final SELECT with and without adding ANALYZE: $ # with ANALYZE: $ sed -e 's/200/200000/' < demo3.sql | sqlite3 Run Time: real 0.647 user 0.639604 sys 0.000000 0|0|0|SCAN TABLE tt 0|1|1|SEARCH TABLE d USING AUTOMATIC COVERING INDEX (m=?) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 2|0|0|SEARCH TABLE d AS d2 USING COVERING INDEX d_ind_1 (m=?) 1|0|0|SEARCH SUBQUERY 2 AS d3 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY Run Time: real 0.000 user 0.000000 sys 0.000000 $ # without ANALYZE: $ sed -e 's/200/200000/' < demo3.sql | sed -e 's/ANALYZE/-- ANALYZE/' | sqlite3 Run Time: real 0.582 user 0.577204 sys 0.000000 0|0|0|SCAN TABLE tt 0|1|1|SEARCH TABLE d USING INDEX d_ind_1 (m=?) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 2|0|0|SEARCH TABLE d AS d2 USING COVERING INDEX d_ind_1 (m=?) 1|0|0|SEARCH SUBQUERY 2 AS d3 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY Run Time: real 0.000 user 0.000000 sys 0.000000
