Suggestion: Use the containment operator to speed up metadata equality about vecs HOT 5 CLOSED

Using the containment operator (@>) for equality should speed up metadata filtering

thanks!

use jsonb_path_ops for a smaller index size. "The non-default GIN operator class jsonb_path_ops does not support the key-exists operators, but it does support @>, @? and @@."

vecs doesn't support key exists filters so that sounds great

--

how does #12 look, does that address both the way you expected?

from vecs.

Just realized it probably shouldn't use containment if the query is a list/tuple or dict, since both the following are true but not equal:

SELECT '{"a": [1, 2, 3]}'::jsonb @> '{"a": [1]}';
SELECT '{"a": {"b": 1, "c": 2}}'::jsonb @> '{"a": {"b": 1}}';

Edit: It could use containment so the index is used, but should also recheck for equality.

from vecs.

Great! fwiw, it could be done in SQL with:

WHERE metadata @> '{"a": [1, 2]}' AND metadata -> 'a' = '[1, 2]'

from vecs.

Yeah, looks great!

from vecs.

makes sense, thanks

I added tests for the various edge cases to make sure its all working properly

The trouble with re-checking for equality in python after the results are returned is that it makes it difficult to get the exact number of results the user requested without potentially fetching more matches from the database. To avoid that, the containment optimization is limited to when the user provides a single value e.g. 6 or "abc".

from vecs.