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 <colei...@gmail.com> wrote:
> 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
> (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.
> sqlite-users mailing list
D. Richard Hipp
sqlite-users mailing list