Re: [sqlite] Index not used in simple alias-like views
Op 24-mei-2010, om 17:59 heeft Dan Kennedy het volgende geschreven: > > On May 24, 2010, at 6:14 PM, Edzard Pasma wrote: > >> Hello, I found a blind spot of the query optimizer. This appears when >> a table is accessed as a view. I think the problem can be phrased as >> "the optimizer failing to push an outer join predicate into a >> view"... This simply means that the following example does not use >> the existing index: >> >> create table t (pk integer primary key); >> create table t2 (fk); >> create view v as select * from t; >> select * from t2 left outer join v on pk = fk; > > Not using the index because of point 3 on this list I would say: > >http://www.sqlite.org/optoverview.html#flattening > Sorry for considering this a shortcoming. By the way, this was observed with version 3.6.23.1. The recently proposed optimizer changes appear to relieve this situation as this is a case for an automatic index. Cheers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index not used in simple alias-like views
On May 24, 2010, at 6:14 PM, Edzard Pasma wrote: > Hello, I found a blind spot of the query optimizer. This appears when > a table is accessed as a view. I think the problem can be phrased as > "the optimizer failing to push an outer join predicate into a > view"... This simply means that the following example does not use > the existing index: > > create table t (pk integer primary key); > create table t2 (fk); > create view v as select * from t; > select * from t2 left outer join v on pk = fk; Not using the index because of point 3 on this list I would say: http://www.sqlite.org/optoverview.html#flattening ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index not used in simple alias-like views
Hello, I found a blind spot of the query optimizer. This appears when a table is accessed as a view. I think the problem can be phrased as "the optimizer failing to push an outer join predicate into a view"... This simply means that the following example does not use the existing index: create table t (pk integer primary key); create table t2 (fk); create view v as select * from t; select * from t2 left outer join v on pk = fk; I hope I may call this a shortcoming of the optimizer and that it will be changed is some future version. Best regards, Edzard. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
