I think without your actual schema and query plan it might be hard to tell, but generally the optimizer is not a panacea, it's limited in what it can divine from your queries.
On Tue, Jun 9, 2015 at 10:52 AM, giles burgess <gilesjb at gmail.com> wrote: > Hi, I have a question about the SQLite query optimizer. > > I have a SQLite embedded database with a star-like schema, with the wrinkle > that each record in the fact table can map to a range of records in each > dimension table. This is represented in the fact table as min and max > rowids for each dimension. > > This is not a particularly friendly physical schema to query directly, so I > have a view that dereferences the rowid foreign keys and also expands the > dimension ranges into separate rows. The multiplication factor for all > dimensions can get quite large, with a single physical fact record mapping > to thousands of records in the view. > > Looking at the query plan, it appears that a select * on this view gets > executed as nested scans over all the dimension tables. > > Obviously this isn't very fast, but I would expect that if I perform a > projection on this view that contains only a subset of the dimension field > values, eg: > > select distinct dimension1_value from friendly_view; > > the query optimizer ought to be able to avoid the joins with the tables for > dimension2, dimension3 etc since they don't contribute to the result. But > according to the planner it's scanning over all dimension tables > regardless. > > Is this expected? Is there some way to define the view so that the > optimizer can avoid these redundant joins? Or do I need to always > explicitly limit the joins to only the tables that are needed? > > > Thanks, > > Giles Burgess > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
