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.
sqlite-users mailing list