> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of Marc L. Allen
> Sent: dinsdag 3 september 2013 15:55
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Inefficient covering index used for Subversion with
> SQLite 3.8.0
>
> Am I understanding that, in this example, the I_NODES_PARENT is being
> chosen as the search index because... it's smaller and therefore faster to
find
> initial qualifying rows that you can then use in some sort of ordered
lookup in
> another index/table?
>
> I'm always in awe of some of the plans a good QA comes up with, and it
> blows me away that there are cases when using a less-covering index would
> be better than a more-covering index.
It also happens to be the first index to be encountered that has wc_id as
first component.
I'm not sure which part (being first vs being smaller) is used to make the
decision, but this is exactly why I expected this to be a simple corner case
bug instead of part of the new query optimizer design.
In Subversion we have queries that only apply on a single directory level
(where the I_NODES_PARENT is used for) vs queries that apply to an entire
tree (in most cases handled via the primary key index). Using the right
index is critical for end-user performance.
The I_NODES_MOVED index is only used when trees are moved, which is an
uncommon operation, but obtaining information about moves is performance
critical in certain scenarios. I hope we will start using the partial index
support for this with the next release. That should also directly invalidate
using this index for these optimizations .
Bert
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
