When upgrading from 3.7.14.1 to 3.24 I noticed the following problem Given a virtual table like
CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER, attr1 INTEGER,...); whose xBestIndex function simulates (in unsupported syntax) CREATE VIRTUAL INDEX vt_key ON vt (key1, key2, key3); but also handles simple comparisons internally, the query SELECT * FROM vt WHERE key1 = <val1> AND key2 BETWEEN <val2> AND <val3> AND attr1 IN (<list>); SQLite 3.7.14.1 asks for (key1,'='), (key2,'>='), (key2,'<=') and xBestIndex accepts all 3 constraints yielding query plan - materialize IN <list> as anonymous ephemeral table - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?) - retrieve column attr1 - search anonymous ephemeral table i.e. perform a single partial table scan on vt and check attr1 SQLite3.24 asks for ...,(attr1,'=') and xBestIndex accepts all 4 constraints yielding - materialize IN (<list>) as anonymous ephemeral table - scan anonymous ephemeral table - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ? AND attr1 = ?) i.e. perform a partial table scan of vt FOR EACH attr1, which is slower by the cardinality of the IN list Fortunately, CTEs come to the rescue: WITH attrs (attr1) AS (VALUES <(list)>) SELECT * FROM vt CROSS JOIN attrs a ON (a.attr1 = vt.attr1) WHERE key1 = <val1> AND key2 BETWEEN <val2> AND <val3> This prevents SQLite 3.24 from adding the last constraint, yielding - materialize IN (<(list)>) as epehemeral table attrs - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?) - retrieve column attr1 - scan ephemeral table attrs The only issue is that the previously generated ephemeral table was implemented as a covering index (allowing the sequence IfNull, Affinity, NotFound) whereas the named ephemeral table is implemented as a table (requiring a full table scan of the ephemeral table, even though at most 1 row can match) Optimisation opportunity: 32 Rewind 1 40 0 00 33 Column 1 0 10 00 r[10]=events.event_type 34 VColumn 0 15 11 00 r[11]=vcolumn(15); atx_txlog.event_type 35 Ne 11 38 10 (BINARY) 53 if r[10]!=r[11] goto 38 36 VColumn 0 6 12 00 r[12]=vcolumn(6); atx_txlog.sync_offset 37 ResultRow 12 1 0 00 output=r[12] 38 Next 1 33 0 01 Could IMHO be rewritten as 32 VColumn 0 15 11 00 r[11]=vcolumn(15); atx_txlog.event_type 33 Rewind 1 40 0 00 34 Column 1 0 10 00 r[10]=events.event_type 35 Ne 11 38 10 (BINARY) 53 if r[10]!=r[11] goto 38 36 VColumn 0 6 12 00 r[12]=vcolumn(6); atx_txlog.sync_offset 37 ResultRow 12 1 0 00 output=r[12] 38 Next 1 33 0 01 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
