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:
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to