Hello,
Suppose one has an expression on the columns of a single table, say x
+y, and that this expression occurs in multiple queries. Then it is
attractive to define it at a single place, using a view:
create view v as select *, x+y as a from t;
I had hoped that substituting such a view in queries instead of the
original table would make performance-wise no difference. This hope is
mostly fulfilled but not completely. If the view appears after an
OUTER JOIN, there is a difference in the query execution plan. The
output is printed below. With the outer join the view is evaluated
into a transient table instead of the underlying table being searched
directly. This can cause a significant slow down.
I understand that a view is treated by the optimizer as a subquery and
that the different execution plan is a matter of Subquery flattening:
http://www.sqlite.org/optoverview.html#flattening
Apparently the optimizer can not produse a flat query here and that is
exactly explained in condition 3:
3. The subquery is not the right operand of a left outer join.
As a casual reader I would think this only needs to apply if the
subquery is not a join. But grasping the complexity of query
optimization, there are likely many other bewares, like aggregation,
union, sub-subqueries, ordering, limits and where clauses. In my case
only the most simple subquery, like the example view, need to be delt
with. Is there any chance that just this is recognized by the optimizer?
Desired change in terms of the list of conditions:
3. The subquery is not the right operand of a left outer join
or the subquery:
- is not a join
- does not use aggregates
- is not DISTINCT
- does not use LIMIT
- does not have a WHERE clause
Does anyone else have the same wish or has this been discussed before?
I would like to know.
Thanks,
Edzard Pasma
Output:
create table t (t integer primary key, x, y);
create table t0 (t0 integer primary key, t, z);
explain query plan
select t.x+t.y from t0 left outer join t using (t) where t0.t0 =
100;
0|0|0|SEARCH TABLE t0 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|1|SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
create view v as select *, x+y as a from t;
explain query plan
select v.a from t0 left outer join v using (t) where t0.t0 = 100;
1|0|0|SCAN TABLE t (~1000000 rows)
0|0|0|SEARCH TABLE t0 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|1|SCAN SUBQUERY 1 (~100000 rows)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
