Re: [Monotone-devel] Monotone speedup by adding additional database indices?

[Markus Schiltknecht]

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