Re: [sqlite] Union all writting on /var/tmp documentation warning
Sorry i didn't realize before that you had:
select * from (... union all ...)
Try with a count(*) as such:
select count(*) from (... union all ...)
And you'll see that both union and "union all" will create a temp file.
Union needs the temp file to remove the duplicates. Union All doesn't
need it at all.
l.
On 29/10/2012 10:37 μμ, Clemens Ladisch wrote:
Eleytherios Stamatogiannakis wrote:
My understanding (and what my experiments have shown) is that in both
cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp.
Okay, let's create a test database ...
$ strace -e trace=open sqlite3 test.db
[...]
sqlite> create table t(x);
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
open("/tmp", O_RDONLY|O_CLOEXEC)= 5
sqlite> begin;
sqlite> insert into t values('long string');
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
... that becomes so big that the subquery overflows the cache:
[...]
sqlite> insert into t select * from t;
open("/var/tmp/etilqs_Oekg82a6826YGdz",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
sqlite> commit;
Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)
sqlite> explain query plan select * from (select rowid, x from t union all
select rowid, x from t) where x = '';
1|0|0|SCAN TABLE t (~10 rows)
2|0|0|SCAN TABLE t (~10 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
sqlite> explain query plan select * from (select rowid, x from t union
select rowid, x from t) where x = '';
2|0|0|SCAN TABLE t (~100 rows)
3|0|0|SCAN TABLE t (~100 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1 (~20 rows)
When executing them, only UNION uses temporary files:
sqlite> select * from (select rowid, x from t union all select rowid, x from
t) where x = '';
sqlite> select * from (select rowid, x from t union select rowid, x from t)
where x = '';
open("/var/tmp/etilqs_QNvTpzSHSedfFFM",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
open("/var/tmp/etilqs_RiTrAL6vrIxpnOu",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Union all writting on /var/tmp documentation warning
Eleytherios Stamatogiannakis wrote:
> My understanding (and what my experiments have shown) is that in both
> cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp.
Okay, let's create a test database ...
$ strace -e trace=open sqlite3 test.db
[...]
sqlite> create table t(x);
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
open("/tmp", O_RDONLY|O_CLOEXEC)= 5
sqlite> begin;
sqlite> insert into t values('long string');
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
... that becomes so big that the subquery overflows the cache:
[...]
sqlite> insert into t select * from t;
open("/var/tmp/etilqs_Oekg82a6826YGdz",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
sqlite> commit;
Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)
sqlite> explain query plan select * from (select rowid, x from t union all
select rowid, x from t) where x = '';
1|0|0|SCAN TABLE t (~10 rows)
2|0|0|SCAN TABLE t (~10 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
sqlite> explain query plan select * from (select rowid, x from t union select
rowid, x from t) where x = '';
2|0|0|SCAN TABLE t (~100 rows)
3|0|0|SCAN TABLE t (~100 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1 (~20 rows)
When executing them, only UNION uses temporary files:
sqlite> select * from (select rowid, x from t union all select rowid, x from
t) where x = '';
sqlite> select * from (select rowid, x from t union select rowid, x from t)
where x = '';
open("/var/tmp/etilqs_QNvTpzSHSedfFFM",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
open("/var/tmp/etilqs_RiTrAL6vrIxpnOu",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Union all writting on /var/tmp documentation warning
My understanding (and what my experiments have shown) is that in both cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp. IMHO, the documentation should warn about this writing behaviour, because for the second case (union all) it isn't expected/predictable because fully buffering is not needed. lefteris. On 29/10/12 20:41, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: "union all" works exactly like plain "union". It always materializes its input. sqlite> explain query plan select 1 union select 2; sele order from deta - 0 0 0 COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) sqlite> explain query plan select 1 union all select 2; sele order from deta - 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) It does not. (In the full "explain" output, "OpenEphemeral" is missing.) Neither with real tables. What particular query behaves unexpectedly for you? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Union all writting on /var/tmp documentation warning
Eleytherios Stamatogiannakis wrote: > "union all" works exactly like plain "union". It always materializes its > input. sqlite> explain query plan select 1 union select 2; sele order from deta - 0 0 0 COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) sqlite> explain query plan select 1 union all select 2; sele order from deta - 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) It does not. (In the full "explain" output, "OpenEphemeral" is missing.) Neither with real tables. What particular query behaves unexpectedly for you? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Union all writting on /var/tmp documentation warning
Look at what that page says about "union all" (emphasis on *all*) --SNIP-- "Note that the UNION ALL operator for compound queries does not use transient indices by itself (though of course the right and left subqueries of the UNION ALL might use transient indices depending on how they are composed.)" --/SNIP-- At least to my eyes, above says what i was expecting before realizing what actually happens, that "union all" tries to not materialize its results when possible. What the truth is, concerning materialization, is that in SQLite "union all" works exactly like plain "union". It always materializes its input. lefteris. On 29/10/12 16:37, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: Can a warning about "union all"'s behaviour of buffering everything in /var/tmp be added in SQLite's documentation? Like this? http://www.sqlite.org/tempfiles.html Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Union all writting on /var/tmp documentation warning
Eleytherios Stamatogiannakis wrote: > Can a warning about "union all"'s behaviour of buffering everything in > /var/tmp be added in SQLite's documentation? Like this? http://www.sqlite.org/tempfiles.html Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Union all writting on /var/tmp documentation warning
Can a warning about "union all"'s behaviour of buffering everything in /var/tmp be added in SQLite's documentation? I think that such a warning could save a lot of time for other SQLite users that trip over the same thing as i did. Thank you, lefteris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
