On Wed, Sep 06, 2017 at 07:43:07PM -0600, Keith Medcalf wrote: > Try the same test using 147 columns in each table. > > 1 column is rather trivial. Even a kindergarten kid could do it in no > time using crayons and the wall. > > [...] > > In other words except in very trivial cases (like having only one > column that is not nullable) it will be very difficult to write a > "correct" JOIN or correlated subquery that emulates an INTERSECT.
Yup. But that doesn't mean that the engine couldn't internally build a result-set from the query without some filtering JOIN, then implement the same strategy as an INTERSECT. You can't do this in SQL if the filter table has different shape than the result set, but the engine might be able to do it. On the other hand, building a complete result set first is... not online behavior. If the result set size is enormous, then the INTERSECT approach is going to make the user very unhappy! I do think OP's tests point out a case where SQLite3 is pessimally picking table scan over covering index scan... ...though scanning the index means that there will be no rowid column in the result, which might actually be a compatibility issue when using rowid tables, so maybe SQLite3 is doing exactly the right thing? I don't think that pessimization is too consequential as users can improve the situation by adding ORDER BY clauses or using WITHOUT ROWID. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
