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

[Nathaniel Smith]

On Thu, Oct 11, 2007 at 11:17:17AM +0200, Markus Schiltknecht wrote:
> 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.

Measurement beats guessing :-).

> 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.

My question was which index it was choosing to use; it is trying to
satisfy a constraint like "A=1, B=2, C=3", and it can pick to either
use an index on A then sequential scan for (B, C), or it can use an
index on (B, C) and then sequential scan for A.  (We do have an index
on (name, value).)  A plausible heuristic would be to use the index
that lets you satisfy more parts of the constraint, but that heuristic
would be wrong in this case, since in fact there are many certs with
NAME=branch, VALUE=net.venge.monotone, but few certs with ID=da39...

-- Nathaniel

-- 
The best book on programming is still Strunk and White.