Can you post, or send by private email, the output from ".fullschema --indent" from the sqlite3 command-line shell on your database file?
On 5/8/18, Charles Leifer <[email protected]> wrote: > Hi, > > I was debugging some discrepancies in execution times of some queries using > the Python 3.6 standard library sqlite3 driver. I was wondering if these > discrepancies could be explained by the fact that the Python sqlite3 driver > is using the legacy sqlite3_prepare interface? > > Just to be clear: I do not think this is a sqlite bug. I would just like to > validate that the behavior I'm seeing is accounted for by the differences > in sqlite3_prepare and sqlite3_prepare_v2 (as I strongly suspect). > > What I noticed was that if I executed the following query, binding the > timestamp value as a parameter, I got very slow execution time (400ms+): > > -- get count of distinct timestamps for given time period. > select count(1) from ( > select 1 from events > where timestamp >= $the_timestamp > group by ip) > > When I tried the query without binding the parameter and just specifying it > inline, the query became incredibly fast (i.e. 2ms). > > Running EXPLAIN QUERY PLAN, I ended up getting two different plans for the > exact same query, depending on whether I passed the timestamp value as a > parameter or directly in the SQL string. > > Parameterized query with timestamp as bound parameter: > > (2, 0, 0, 'CO-ROUTINE 0x1EB3C80'), > (10, 2, 0, 'SCAN TABLE events AS t1 USING INDEX event_ip'), > (36, 0, 0, 'SCAN SUBQUERY 0x1EB3C80 AS _wrapped') > > Query with the timestamp expressed in-line: > > (2, 0, 0, 'CO-ROUTINE 0x1EAFAE0'), > (10, 2, 0, 'SEARCH TABLE events AS t1 USING INDEX events_timestamp > (timestamp>?)'), > (14, 2, 0, 'USE TEMP B-TREE FOR GROUP BY'), > (43, 0, 0, 'SCAN SUBQUERY 0x1EAFAE0 AS _wrapped') > > Now, about this table... it's got two indexes, as you could probably > gather: one on the IP column and one on the timestamp column. I also have > the stat4 module included in my build. > > It seems that when the timestamp is passed as a bound parameter, SQLite > prefers the index on IP. But when the timestamp is expressed inline, the > query planner decides to filter on timestamp *first*, then do the group-by > IP (which ends up being a better plan for this use-case). > > I do not think this is a bug in SQLite! I am simply curious if the behavior > differences between sqlite3_prepare and sqlite3_prepare_v2 could account > for this difference? > > Thanks so much for the wonderful library. > > Charlie > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
