If your xBestIndex function indicates that your virtual table supports an index on the constraint with cost x and you have a single OR clause, the QP will assign a cost of 2*x to performing 2 keyed lookups/partial table scans
If your XbestIndex function indicates that your virtual tabel does no support an index on the constraint, the QP will revert to a full table scan with a subsequent check of the constraints. The option of creating an index "on the fly" does not exist for virtual tables. An IN clause is always implemented as an ephemeral table (the ONCE opcode ensures that the values given are inserted before the first query), so this forces SQLite to use a full table scan of the virtual table. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave Gesendet: Samstag, 21. Oktober 2017 02:07 An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables.... > Behalf Of Dan Kennedy > Sent: Tuesday, October 17, 2017 11:58 AM ... > > I think the exception is queries with OR terms. With FTS[345], if you > do something like: > > CREATE VIRTUAL TABLE t1 USING fts5(x); > EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def'; > > You can see the Rowid opcodes. > > SQLite runs two separate queries on the virtual table - one with > "MATCH 'abc'" and the other with "MATCH 'def'". It uses the rowids for > each matched row to avoid returning duplicates. If the xRowid method > always returned 0, then only the first set of matches would be > returned (because SQLite would deem the second set to be duplicates of > the first). Or if xRowid returned arbitrary values your results might > include duplicates. etc. > > Same applies to other virtual table types. ... FYI FWIW, I had a moment to play with this a little. I was able to reproduce Dan's case, however I'm not so sure that it is due to the OR (or at least not only that). I think it maybe has more to do with the OR of MATCH's. I tried with one of my vtables using an 'OR' clause, and I got no rowid opcodes. I was using equality, however. There were two scenarios: 1) OR clause on a column that is indexed this generated two table scans, with different filter values 2) OR clause on a column that was /not/ indexed this generated one table scan, with both conditionals evaluated on the same row Those seemed like sane plans. For fun I also tried 'IN' with the exact same results. I'm less familiar with MATCH, but I understand what Dan is saying about de-duping. I don't understand why the planner would have chosen to realize OR as a set union, but I'm sure it has it's reasons. I should study the query planner implementation one day when I have some time.... Cheers! -dave _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ 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
