Re: [sqlite] Poor query planning for virtual tables
Hello! On Friday 09 April 2010 18:27:52 Roger Binns wrote: > > Virtual table _doesn't_ use any index but query planner show this: > > 0|2|TABLE role_fts VIRTUAL TABLE INDEX 0: > > It is showing the results of the BestIndex virtual table method. Thanks. So the problem is in the FTS3 extension only. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
On 04/09/2010 12:27 AM, Alexey Pechnikov wrote: > Virtual table _doesn't_ use any index but query planner show this: > 0|2|TABLE role_fts VIRTUAL TABLE INDEX 0: It is showing the results of the BestIndex virtual table method. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
Hello! On Friday 09 April 2010 01:28:18 Jay A. Kreibich wrote: > Exactly like every other query the database makes? Why would > this be different? Virtual table _doesn't_ use any index but query planner show this: 0|2|TABLE role_fts VIRTUAL TABLE INDEX 0: Why planner don't show full-teable scan as "TABLE role_fts VIRTUAL TABLE"? Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
On Fri, Apr 09, 2010 at 12:50:56AM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > May be is needed to show this more informatively. The record > "TABLE role_fts VIRTUAL TABLE INDEX 0:" is not useful I think. http://sqlite.org/fts3.html 1.3 Simple FTS3 Queries [...] FTS3 tables can be queried efficiently using SELECT statements of two different forms: - Query by rowid [...] the form "rowid = ?" [...] - Full Text query [...] MATCH [...] If neither of the two query strategies enumerated above can be used, all queries on FTS3 tables are implemented using a linear scan of the entire table. Although you've got rowid= type queries, they're JOINs, not searches for specific values, so the first point doesn't really count. I suppose there is some chance the query planner is assuming any column named "rowid" is a cheap lookup, and somehow favoring the FTS virtual table in the JOIN sequence, but that seems like a bit of a long-shot. > And is not helpful than planner using full table scan without any > alert. Exactly like every other query the database makes? Why would this be different? > So vurtual tables are similar to regular tables but we have a lot of > hidden planner problems. Virtual tables are *not* similar to regular tables. They're highly specialized, and each type of virtual table is specialized in a different manner. If you treat them like normal tables you're going to have lots of problems. If you want something that looks and acts like a normal table... use a normal table. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
Hello! On Friday 09 April 2010 00:11:56 Jay A. Kreibich wrote: > Virtual Tables are completely abstract to the query planner. The > only way to do any type of indexed or high efficiency lookup is if > the virtual table allows that to happen. Otherwise, there is > nothing the query planner can do, except perform a full table scan > -- even on the ROWID column. The issue isn't with the SQLite core, > it isn't with the query planner-- it is with the virtual table module > you're using. Most modules are designed to be used in specific ways. > If you're using it in some other way, it isn't a surprise it doesn't > work very well. May be is needed to show this more informatively. The record "TABLE role_fts VIRTUAL TABLE INDEX 0:" is not useful I think. And is not helpful than planner using full table scan without any alert. For FTS3 the workaround is in using _content table insted of virtual table but now we must search for bad-planned queries manually. So vurtual tables are similar to regular tables but we have a lot of hidden planner problems. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
On Thu, Apr 08, 2010 at 11:52:13PM +0400, Alexey Pechnikov scratched on the wall: > > 1. With ordinary tables - fast > 2. With ordinary tables and virtual table - very slow > > select role_exist.rowid as rowid, role.* from role_exist, role, role_fts > where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; > ^CCPU Time: user 34.058128 sys 1.720108 > Error: interrupted Is there a point you're trying to make, or a question you're asking? All you've done is posted two times that aren't that unexpected. Virtual Tables are completely abstract to the query planner. The only way to do any type of indexed or high efficiency lookup is if the virtual table allows that to happen. Otherwise, there is *nothing* the query planner can do, except perform a full table scan -- even on the ROWID column. The issue isn't with the SQLite core, it isn't with the query planner-- it is with the virtual table module you're using. Most modules are designed to be used in specific ways. If you're using it in some other way, it isn't a surprise it doesn't work very well. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Poor query planning for virtual tables
Hello! The SQLite library is builded from trunk af3b72d94a1b6513f02402af3ada5fb5dd390151 1. With ordinary tables - fast explain query plan select role.rowid as rowid, role.* from role_exist, role where role_exist.rowid=role.rowid; 0|0|TABLE role_exist 1|1|TABLE role USING PRIMARY KEY select role.rowid as rowid, role.* from role_exist, role where role_exist.rowid=role.rowid; ... CPU Time: user 0.00 sys 0.00 2. With ordinary tables and virtual table - very slow explain query plan select role_exist.rowid as rowid, role.* from role_exist, role, role_fts where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; 0|2|TABLE role_fts VIRTUAL TABLE INDEX 0: 1|0|TABLE role_exist USING PRIMARY KEY 2|1|TABLE role USING PRIMARY KEY select role_exist.rowid as rowid, role.* from role_exist, role, role_fts where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; ^CCPU Time: user 34.058128 sys 1.720108 Error: interrupted Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
