Description of changes

Add indexes to sqlite metadata value columns.

This greatly improves the performance of queries that filter based on equality or comparison to metadata fields:

Note that the index only improves performance when the filter is highly selective. If there are large numbers of records that match the filter, processing is still slow (as they must all realized in memory and passed to the vector search.)

Migration Plan

Creating the indexes on a database of ~40,000 embeddings with ~600,000 metadata rows takes less than ten seconds. It will seamlessly be applied to existing databases the first time they are loaded.

The change should be both forward and backward compatible. The only effect of the presence or absence of indices should be on the underlying sqlite query plan: result set semantics should be invariant according to SQL semantics.

Downsides

This change does have an small constant effect on insert speed:

It also yields somewhat larger sqlite database sizes: 1.7gb vs 1.5gb, for the scenario where each embedding has 15 metadata fields (5 ints, 5 floats, 5 strings of 1-10 words)

Test plan

Unit and property tests pass in CI

Documentation Changes

None required

Aug 02 '24 19:08 levand

Reviewer Checklist

Please leverage this checklist to ensure your code review is thorough before approving

Testing, Bugs, Errors, Logs, Documentation

[ ] Can you think of any use case in which the code does not behave as intended? Have they been tested?
[ ] Can you think of any inputs or external events that could break the code? Is user input validated and safe? Have they been tested?
[ ] If appropriate, are there adequate property based tests?
[ ] If appropriate, are there adequate unit tests?
[ ] Should any logging, debugging, tracing information be added or removed?
[ ] Are error messages user-friendly?
[ ] Have all documentation changes needed been made?
[ ] Have all non-obvious changes been commented?

System Compatibility

[ ] Are there any potential impacts on other parts of the system or backward compatibility?
[ ] Does this change intersect with any items on our roadmap, and if so, is there a plan for fitting them together?