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
