[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

From: Ralf S. Engelschall
Subject: Re: [Monotone-devel] Monotone speedup by adding additional database indices?
Date: Thu, 11 Oct 2007 09:32:59 +0200
User-agent: Mutt/1.5.16 OpenPKG/CURRENT (2007-06-09)

On Wed, Oct 10, 2007, Nathaniel Smith wrote:

> [...]
> [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?]

Monotone showed just _warning_ messages but operated just fine.

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

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.

> 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?
> [...]

We can check this with a manual "EXPLAIN <query>", I think.

                                       Ralf S. Engelschall

reply via email to

[Prev in Thread] Current Thread [Next in Thread]