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

[Nathaniel Smith]

On Wed, Oct 10, 2007 at 08:07:26PM +0200, Ralf S. Engelschall wrote:
> What do we think? Should we investigate further and especially add
> additional indices like the above to the Monotone database schema? Or is
> there consensus that this type of speed optimization is just the root of
> furthcoming evil and at least at this time should be still ignored at
> all...

No way, 5x speedups for 1 line of code = ++good.  And while once upon
a time the database upgrade machinery was not savvy to indexes, that
got fixed long ago (exactly to add some similar indexes, IIRC).  We
just don't revisit whether we have the right indexes very often :-).
(This isn't to override any more specific problems people might raise
in this thread, though I didn't see any obvious showstoppers so far.)

[Err... though... waittasec.  Shouldn't the database upgrade machinery
be causing mtn to bomb out on your modified database ("unrecognized
schema version" or the like)?  Did you disable that or something?]

I do wonder where the benefit is coming from in this particular case.
The index on public_keys is almost certainly just irrelevant (though
it doesn't hurt and adds scalability), since you probably don't have
more than, say, 100 keys in there, and the whole table is almost
certainly cached.

And on revision_certs we already have:

CREATE INDEX revision_certs__id ON revision_certs (id);
CREATE INDEX revision_certs__name_value ON revision_certs (name, value);

So if we add an index on (id, <anything>) we should remove the index
on simple (id).  But also that index on simple (id) should be making
that search fast already, because once you've located the certs
for a particular rev, then you only have to do a sequential scan over
4 of them (in most cases) to find any particular one.  Perhaps
sqlite's optimizer has gotten *too* smart and is picking the wrong
index, doing a lookup by (name, value) and then sequential scan to
match the id?

...It may not be worth answering these questions as opposed to just
adding the stupid index, though.

-- Nathaniel

-- 
Details are all that matters; God dwells there, and you never get to
see Him if you don't struggle to get them right. -- Stephen Jay Gould