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
