Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven:
RSmith wrote:
On 2013/07/21 12:01, E.Pasma wrote:
Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~1000000 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~333333 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~333333 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~333333 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~333333 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~333333 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~333333 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
Well yes, the plan does not read like one would expect an optimal
plan
to read like - but to the purpose of the original request there is no
more-optimal a plan, is there?
SQLite always attempts to flatten such subqueries:
<http://www.sqlite.org/optoverview.html#flattening>
However, this can be counterproductive when the values in the subquery
are subquery lookups themselves; storing the values once in a
temporary
table might be faster.
To prevent SQLite from flattening, break one of the rules from the
link
above; for example, add an OFFSET clause:
select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
limit -1 offset 0
)
order by c;
1|0|0|SCAN TABLE categories (~1000000 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot1 (~333333 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot2 (~333333 rows)
0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
Regards,
Clemens
Is a change in SQLite imaginable such that column expressions are not
re-evaluated with each reference to the column alias?
This could also improve queries that use aliases only in the order by
clause, like
select id, (subquery) as c from categories order by c;
Sorry if this is beyond the subject of thie thread,
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
