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

[Ralf S. Engelschall]

Some Monotone operations really operate slower than what one would
expect in the first spot. Hence, I've today looked at the run-time of
a simple "mtn update" in a workspace which *is already* at h:n.v.m.
This "no-operation" command internally performs a dozend times the
following SQL queries:

  SELECT id, name, value, keypair, signature
         FROM revision_certs WHERE id = ? AND name = ? AND value = ?
  SELECT keydata FROM public_keys WHERE id = ?
  SELECT id FROM public_keys WHERE id = ?

The problem is that "revision_certs" and "public_keys" have not the
proper indices for those queries and hence full-table scans seem to
be performed. I did a quick test and added the following to indices
manually:

  CREATE INDEX revision_certs__id_name_value ON
               revision_certs (id, name, value);
  CREATE INDEX public_keys__id ON
               public_keys (id);

This dropped down the total execution time of the mentioned "mtn update"
command by over 80%! A "time mtn update" showed 0.450s on average before
and 0.080s on average afterwards. And this was really not any type of
in-depth analysis of the situation. I just created two obvious indices
for the most prominent queries which "mtn --debug update" showed me.

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...
                                       Ralf S. Engelschall
                                       address@hidden
                                       www.engelschall.com