Filtered Vector Seek in MyScale

Vector databases be offering lightning-fast retrieval on an identical gadgets saved in between billions of information. On the other hand, you may additionally be inquisitive about looking for similar gadgets that fit a particular set of stipulations, referred to as filtered vector seek. With lend a hand from MyScale(opens new window), you’ll be able to spice up your filtered vector searches to a brand new stage.

Maximum vector indexes or vector shops paintings as devoted index services and products. They beef up a partial filtered vector seek implementation of MongoDB question and projection operators (opens a brand new window the place you’ll be able to enter a dictionary of stipulations.

Supported information sorts and comparators vary between implementations, however maximum interfaces simplest beef up strings, integers on equals, and fundamental worth comparisons. In contrast to databases, those vector indexes don’t seem to be designed to take care of advanced information sorts and stipulations. Because of this, you want an exterior database method to retailer this information, however you can’t use this information to accomplish filtered vector searches. This answer works, however it’s difficult and has barriers.

In reality, there can and must be a greater answer. Vector seek may also be incorporated with a database to make it extra powerful than it’s now. MyScale can concurrently take care of filtered vector seek with advanced stipulations and information sorts the usage of the usual WHERE clause.

Pre-Filtering and Submit-Filtering

Filtered vector seek implementations may also be categorised into two sorts:

  1. Pre-Filtered Vector Seek
  2. Submit-Filtered Vector Seek

As an example:

Believe you’ve a desk containing the chat historical past for customers Jack, Jan, and John, and also you wish to use a filtered vector seek question to retrieve Jack’s chat historical past very similar to the given question vector.

Word: Each and every document has a person mark and have vector—for simplicity, we flip vectors into numbers.

The next symbol describes each a NoSQL and SQL question retrieving Jack’s chat historical past:

 NoSQL and SQL query retrieving Jack’s chat history

Either one of the ones queries include a clear out at the person Jack. On the other hand, this clear out may also be structured another way, relying at the implementation.

  1. Pre-filtered vector seek: For the pre-filtered vector seek, the engine will first scan the information and simplest retain information that fit the given clear out situation. As soon as this scan is whole, the engine will carry out the vector seek at the pre-filtered applicants.
  2. Submit-filtered vector seek: However, the post-filtered vector seek will first carry out the vector seek after which clear out those effects in accordance with the given clear out situation.

vector search

Between those two strategies, pre-filtering is healthier than post-filtering in accuracy and meets what we think from a filtered vector seek. Maximum vector databases beef up pre-filtering with vector seek. On the other hand, this pre-filtering does not come at no cost. On the other hand, pre-filtering isn’t loose, expanding computation and dragging the filtered vector seek’s efficiency. Maximum implementations be afflicted by both efficiency or clear out barriers, comparable to information garage and supported comparator limits.

MyScale makes use of a column-based garage engine (opens new window)tailored from the ClickHouse MergeTree engine(opens recent wind, which is super-fast on typical filters, considerably boosting first-stage filtering and making the filtered vector seek sooner than different implementations. Moreover, you’ll be able to use a easy SQLWHERE clause to outline filters on any column for your desk.

What You Can Do With the The place Clause in MyScale

As MyScale is evolved on most sensible of Clickhouse(opens a brand new window), it provides exactly the similar capability as ClickHouse.

As an example:

Manner Others MyScale
eq / neq
ge / gt / lt / le
come with/exclude
with string trend fit LIKE
with serve as
with arrayFunction (opens new window)
with subqueries

Let’s take a look at a number of examples highlighting what MyScale’s WHERE clause is able to. You’ll be able to to find the code for those examples in our Colab or GitHub areas

Commonplace Worth Comparability: =, !=, >, <, >=, <=

Maximum vector index answers beef up those operations on strings or numbers. In MyScale, you’ll be able to write worth comparisons with:

Word: The column sort and price will have to be the similar.

When you’ve got a couple of stipulations so as to add to the WHERE clause, use logical operators like AND to glue them:

MyScale additionally helps set operations like IN and NOT IN:

Operators for Arrays

You’ll be able to take a look at if a component is in an array with the has serve as:

You’ll be able to fit string patterns in MyScale with the key phrase LIKE:

Date-Time Comparability

MyScale additionally features a date-time comparability serve as:

Geo-Knowledge Comparability

MyScale can take care of the H3 Index (opens new window)and S2 Geometry(opens new window, tough gear for direction making plans and geometry research.

For instance, with the H3 Index, you’ll be able to use the world of a hexagon to clear out geographical information in a given space:

MyScale permits you to retailer JSON as an object and clear out on its attributes.

You’ll be able to use the JSON information sort to import a JSON string right into a desk and use the WHERE clause underneath to clear out effects:

Worth Purposes

MyScale contains a whole lot of column information processing purposes that you’ll be able to make the most of in WHERE clauses, comparable to:

Array purposes are actually tough, particularly with our vector seek. In our documentation(opens a brand new window, we presented array purposes in MyScale for the overall logit computations and gradient computations for our few-shot classifier.

Clickhouse has nice documentation on array purposes(opens a brand new window.

Subquery Make stronger

Subqueries are queries inside queries. You’ll be able to additionally write a WHERE clause with any other SELECT question as follows:

We investigated filtered vector seek efficiency in vector-db-benchmark(opens new window). We used laion-768-5m-ip-probability the place a random go with the flow as its clear out mark throughout the question. We additionally examined common vector database answers in opposition to MyScale. As the next chart describes, MyScale surpasses maximum different vector database answers in that we offer higher accuracy with upper throughput.

Moreover, MyScale achieves the most productive cost-efficiency of all vector databases examined when accuracy >= 90%. When in comparison to different SQL-integrated vector databases — comparable to pgvector and — MyScale may be the simplest SQL and vector-integrated database that achieves production-ready accuracy and throughput for filtered seek.

In abstract, MyScale provides higher accuracy with upper throughput at a cheaper price. We additionally beef up 5 million vectors with extra information sorts and purposes inside our s1 pod within the product line, which is loose for all registered customers.


Filtered seek is a not unusual form of question in vector databases that permits you to seek for an identical vectors or information issues in accordance with particular standards or filters, particularly when coping with information that may be represented as vectors, comparable to textual content and symbol embeddings or different structured information.

MyScale embeds SQL energy into AI era; filtered seek is a living proof, enabling extra subtle and versatile querying functions for vector databases. Via combining AI and SQL, you’ll be able to carry out advanced information operations and searches, making extracting precious insights, finding patterns, and acting other analytical duties more straightforward.

You May Also Like

More From Author

+ There are no comments

Add yours