[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Monotone-devel] Monotone speedup by adding additional database indi
From: |
Markus Schiltknecht |
Subject: |
Re: [Monotone-devel] Monotone speedup by adding additional database indices? |
Date: |
Thu, 11 Oct 2007 11:17:17 +0200 |
User-agent: |
Icedove 1.5.0.12 (X11/20070730) |
Hi,
Ralf S. Engelschall wrote:
Monotone showed just _warning_ messages but operated just fine.
Strange, the following error message should be returned:
mtn: misuse: /home/markus/.monotone.36.ind.db appears to be a monotone
database, but this version of
mtn: misuse: monotone does not recognize its schema.
mtn: misuse: you probably need a newer version of monotone.
In general, yes. The (id, name, value) index should be consulted
by SQLite also in case only "id" has to be looked up for a query.
As a quick test, I did add the new, combined index and dropped the old
one (on id only). Doing that, my db has grown by about 2.4 percent:
-rw-r--r-- 1 markus markus 617M 2007-10-11 10:33 .monotone.36.db
-rw-r--r-- 1 markus markus 632M 2007-10-11 10:38 .monotone.36.ind.db
But I doubt very much that there are any gains. The index on
revision_certs(id) should be enough, as we have only few (in most cases
four) revision_certs per revision id.
And AFAICT sqlite optimizes properly, at least the explain result looks
good:
# sqlite3 .monotone.36.db "EXPLAIN SELECT id, name, value FROM
revision_certs WHERE id = 'abc' AND name = 'def' AND value = 'ghi';" >
e1.txt
# sqlite3 .monotone.36.ind.db "EXPLAIN SELECT id, name, value FROM
revision_certs WHERE id = 'abc' AND name = 'def' AND value = 'ghi';" >
e2.txt
The two execution plans differ only slightly:
*** e1.txt 2007-10-11 10:53:15.492029559 +0200
--- e2.txt 2007-10-11 10:53:23.128009459 +0200
***************
*** 1,26 ****
0|Goto|0|22|
1|Integer|0|0|
! 2|OpenRead|1|28|keyinfo(5,BINARY,BINARY)
! 3|SetNumColumns|1|6|
! 4|String8|0|0|def
5|IsNull|-1|20|
! 6|String8|0|0|abc
7|IsNull|-2|20|
8|String8|0|0|ghi
9|IsNull|-3|20|
! 10|MakeRecord|3|0|bbbbb
11|MemStore|0|0|
12|MoveGe|1|20|
13|MemLoad|0|0|
14|IdxGE|1|20|+
! 15|Column|1|1|
! 16|Column|1|0|
17|Column|1|2|
18|Callback|3|0|
19|Next|1|13|
20|Close|1|0|
21|Halt|0|0|
22|Transaction|0|0|
! 23|VerifyCookie|0|26|
24|Goto|0|1|
25|Noop|0|0|
--- 1,26 ----
0|Goto|0|22|
1|Integer|0|0|
! 2|OpenRead|1|39|keyinfo(3,BINARY,BINARY)
! 3|SetNumColumns|1|4|
! 4|String8|0|0|abc
5|IsNull|-1|20|
! 6|String8|0|0|def
7|IsNull|-2|20|
8|String8|0|0|ghi
9|IsNull|-3|20|
! 10|MakeRecord|3|0|bbb
11|MemStore|0|0|
12|MoveGe|1|20|
13|MemLoad|0|0|
14|IdxGE|1|20|+
! 15|Column|1|0|
! 16|Column|1|1|
17|Column|1|2|
18|Callback|3|0|
19|Next|1|13|
20|Close|1|0|
21|Halt|0|0|
22|Transaction|0|0|
! 23|VerifyCookie|0|29|
24|Goto|0|1|
25|Noop|0|0|
I'm not an expert reading these plans, but for sure both variants use an
index scan and not a sequential scan. Thus I don't think it's worth
changing these revision_certs indices.
Regards
Markus
- [Monotone-devel] Monotone speedup by adding additional database indices?, Ralf S. Engelschall, 2007/10/10
- Re: [Monotone-devel] Monotone speedup by adding additional database indices?, Nathaniel Smith, 2007/10/11
- Re: [Monotone-devel] Monotone speedup by adding additional database indices?, Ralf S. Engelschall, 2007/10/11
- [Monotone-devel] Re: Monotone speedup by adding additional database indices?, Lapo Luchini, 2007/10/11
- [Monotone-devel] Re: Monotone speedup by adding additional database indices?, Lapo Luchini, 2007/10/11
- Re: [Monotone-devel] Re: Monotone speedup by adding additional database indices?, Markus Schiltknecht, 2007/10/12
- [Monotone-devel] Re: Monotone speedup by adding additional database indices?, Lapo Luchini, 2007/10/12
[Monotone-devel] Re: Monotone speedup by adding additional database indices?, Lapo Luchini, 2007/10/11