On Thu, Oct 20, 2011 at 12:09:25PM +0200, Sam scratched on the wall: > Hi, > I'm developing a simple virtual table module and I've found possible > misbehavior of virtual table modules when used with IN condition.
> select x from y where x in (3) > > calls xFilter without any arguments, and vtab module is not able to apply > it's filtering rules, what results in degraded performance. > Is this a feature or bug ? I wouldn't consider it a bug, although it might be considered a limitation of the xFilter API. Given that IN is typically used with a list of arguments (e.g. "...where x in (3, 7, 16)..."), how would you propose xFilter be called? You can't pass multiple arguments. While this IN expression can be translated into "...where (x=3 or x=7 or x=16)...", again-- there isn't any easy way to pass that type of compound sub-condition to xFilter. In this case, SQLite might make three distinct passes of the v-table, asking for x=3, x=7, and x=16 in turn, and then reassembling the rows internally, but that would be even more costly if the v-table can't filter on that column. The only trivial case is when there is only a single argument, and I'm not sure that's worth special-casing. Mostly it comes down to the optimizer's limited view of what types of "indexes" and conditions a v-table can efficiently process. Without a very detailed understanding of the access efficiency, it is often better to just fall back to the safest plan, which is to scan the v-table once and deal with all the conditionals internally. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
