Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
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
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
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
> -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Hick Gunter > > In our virtual table implementations, we are using the rowid > to return the location of the record in the backing store > (e.g. record offset in the file used as a backing store, > offset within a shared memory section or maybe even the > memory address of the record image) and also implement fast > lookup by rowid. > > If you don't require such ability, you may as well return a > constant, a global counter value or a counter that is reset > in the xFilter function. > > So, YES you always have to implement the xRowid method. > > It will only get called if your SELECT statement explicitly > mentions it. No "INTEGER PRIMARY KEY" magic is performed for > virtual tables. > Thanks for your input as well; I somehow missed it until just now. Cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
> Behalf Of Dan Kennedy > Sent: Tuesday, October 17, 2017 11:58 AM > > On 10/17/2017 01:22 PM, Hick Gunter wrote: > > In our virtual table implementations, we are using the > rowid to return the location of the record in the backing > store (e.g. record offset in the file used as a backing > store, offset within a shared memory section or maybe even > the memory address of the record image) and also implement > fast lookup by rowid. > > > > If you don't require such ability, you may as well return a > constant, a global counter value or a counter that is reset > in the xFilter function. > > > > So, YES you always have to implement the xRowid method. > > > > It will only get called if your SELECT statement explicitly > mentions it. No "INTEGER PRIMARY KEY" magic is performed for > virtual tables. > > 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. > > Dan. Yikes, thanks for the insight on that OR use-case; I'll have to do some analysis to see what is my exposure. The counter trick is an interesting suggestion, but I guess I am still at-risk because I have to make it deterministic/repeatable at least in the context of a statement, which can still a challenge. E.g. in your OR clause example, if the query engine does indeed do two table scans (as opposed to one table scan, and computing all the predicates) then I have some risk that the two scans return different results (since my data is coming from APIs, and dynamic, rather that persisted collections). In other projects I've definitely used the counter trick before, caching the underlying data (to support updates and transactions) but those were known to be small datasets. This stuff coming from APIs could be big, so I wanted to avoid caching it all. But one does what one must Thanks for all the feedback! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
On 10/17/2017 01:22 PM, Hick Gunter wrote: In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast lookup by rowid. If you don't require such ability, you may as well return a constant, a global counter value or a counter that is reset in the xFilter function. So, YES you always have to implement the xRowid method. It will only get called if your SELECT statement explicitly mentions it. No "INTEGER PRIMARY KEY" magic is performed for virtual tables. 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. Dan. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave Gesendet: Montag, 16. Oktober 2017 21:23 An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables Hi, I am building a system which involves a number of virtual table implementations. They are all read-only, but will be involved in a bunch of joins amongst themselves. My question is this: the documentation http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to be always required to be implemented. But does it really? Is it ever used for read-only tables? I have never seen it invoked, and I have been blithely ignoring implementing it, but I wonder if there is a case where it would be invoked for a read-only query and so I am tempting fate. I ask in particular because implementing it will be quite awkward for the underlying implementation in my case, and I'd very much prefer to skip it. Even a 'without rowid' table would imply specifying some primary key, which in a few cases would also be awkward. Thanks in advance, -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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast lookup by rowid. If you don't require such ability, you may as well return a constant, a global counter value or a counter that is reset in the xFilter function. So, YES you always have to implement the xRowid method. It will only get called if your SELECT statement explicitly mentions it. No "INTEGER PRIMARY KEY" magic is performed for virtual tables. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave Gesendet: Montag, 16. Oktober 2017 21:23 An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables Hi, I am building a system which involves a number of virtual table implementations. They are all read-only, but will be involved in a bunch of joins amongst themselves. My question is this: the documentation http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to be always required to be implemented. But does it really? Is it ever used for read-only tables? I have never seen it invoked, and I have been blithely ignoring implementing it, but I wonder if there is a case where it would be invoked for a read-only query and so I am tempting fate. I ask in particular because implementing it will be quite awkward for the underlying implementation in my case, and I'd very much prefer to skip it. Even a 'without rowid' table would imply specifying some primary key, which in a few cases would also be awkward. Thanks in advance, -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
