Re: [Monotone-devel] speed of 0.99

[Markus Wanner]

Timothy,

On 11/30/2010 01:49 AM, Timothy Brownawell wrote:
> Monotone never calls ANALYZE. Eventually I suppose we might want to put
> a rule in place to do this occasionally (every time the revision count
> has doubled?). Or maybe there aren't enough tables with multiple usable
> indexes that it would matter much; if revision_certs is the only example...

Judging from the docs, it might make sense to run ANALYZE after a db
migration, which would be easy enough for monotone.

> Some things (such as netsync and the b: selector) need to list all
> revisions in a particular branch, those look up on (name, value). So we
> still want an index with those leading.

Assuming the usual use case of monotone, close to a fourth of all rows
in revision_certs match one of the usual cert names. When SELECTing one
of those, I think it's better reading (most of) revision_certs into the
cache, rather than a duplicate of it.

Duplication is costly. Probably not for the micro-benchmark you are
currently optimizing for, but certainly overall. Because usually, you
don't just do 'mtn ls tags' or 'mtn sync', but other operations often
follow, which then benefit from not having to read another duplicate
from disk.

> No, that's using the INDEXED BY requirement to make it use a (rev_id,
> name, value) index. If it hits the low-selectivity (name, ...) index it
> takes ~10s.

That's part of why the current ordering for the uniqueness constraint
doesn't make much sense.

About the only use for that index in monotone is when we scan for 'name
= ? AND value = ?'. But interestingly, one of the two methods that does
this also SELECTs the hash attribute, so the index alone doesn't even
provide enough data and sqlite needs to fetch the matching tuples from
revision_certs.

I propose to re-order the uniqueness constraint to (revision_id, value,
name, keypair_id, signature) and maybe add an index on (value) - and
only that to avoid duplication.

Regards

Markus Wanner