Vector retrieval

What is vector retrieval

Vector retrieval finds the K vectors nearest to a query vector under a chosen distance or similarity measure (K-nearest neighbors, KNN). It is used in image, text, and speech retrieval and recommendation systems. Scalar search filters structured fields by conditions, while vector search ranks vectors, often generated from unstructured data, by similarity. A full scan can find the exact top K for the chosen measure; an approximate nearest-neighbor index may trade recall for speed.

Comparison

Scalar search

Vector search

Stored data

Structured fields, such as age

Vectors or embeddings, often derived from unstructured data

Query

Exact condition: SELECT * FROM people WHERE age = 18;

Nearest neighbors: SELECT id, l2_distance(embedding, '[0.3,0.3,0.3,0.3,0.3]') AS distance FROM items ORDER BY distance ASC LIMIT 3;

Result

Rows satisfying the condition, such as name = 'x', age = 18

The three nearest rows, such as id = 'C', distance = 1.76717, ordered from nearest to farthest

Matrixone currently supports vector retrieval using the following distance measure functions:

Note

Vector indexes support multiple distance metrics, including vector_l2_ops, vector_ip_ops, vector_cosine_ops, and vector_l2sq_ops. HNSW and other index types may support different subsets of these metrics; see the relevant index documentation for details. The l1_distance() SQL function is not available in the current runtime.

Minimal index-backed workflow

For a quick, self-contained KNN test, follow this sequence:

  1. Create a table with a vector column and insert the sample vectors.

  2. Create an IVFFLAT index after the table has data. See CREATE INDEX USING IVFFLAT for the index syntax and tuning options.

  3. Query with ORDER BY l2_distance(vector_column, query_vector) ASC; use LIMIT when only the nearest results are needed.

  4. Check the returned rows, then drop the temporary table when the test is complete.

The complete runnable example is in CREATE INDEX USING IVFFLAT. The Iris example below is a larger, file-backed example and is not required for a minimal vector-search test.

Application scenarios for vector retrieval

Having vector capability in a database means that the database system has the ability to store, query, and analyze vector data. These vectors are often associated with complex data analysis, machine learning, and data mining tasks. Here are some application scenarios where the database has vector processing power:

  • Generative AI applications: These databases can serve as the backend for generative AI applications, enabling them to obtain nearest neighbor results based on user-supplied queries, improving output quality and relevance.

  • Advanced object recognition: They are invaluable for developing advanced object recognition platforms that recognize similarities between different data sets. This has practical applications in areas such as plagiarism detection, facial recognition and DNA matching.

  • Personalized recommendation systems: Vector databases can enhance recommendation systems by integrating user preferences and choices. This will result in more accurate and targeted recommendations that improve the user experience and engagement.

  • Anomaly detection: A vector database can be used to store feature vectors representing normal behavior. The anomaly can then be detected by comparing the input vector with the storage vector. This is useful in cybersecurity and industrial quality control.

  • Marketing optimization: Through the analysis and mining of user data, vector database can realize personalized recommendations, customer segmentation and market trend forecasting, and other functions to provide enterprises with accurate marketing strategies.

  • Natural language processing: Vector database can process large-scale text data, realize semantic similarity search, text classification, document clustering and other natural language processing tasks, widely used in intelligent customer service, public opinion analysis and other fields.

  • Semantic Search and Retrieval: In applications involving large language models, vector databases can store and retrieve massive amounts of text vectors, and intelligent text matching and semantic search can be achieved by calculating similarities between vectors.

Examples

The Iris dataset is a well-known multi-class taxonomic dataset that can be searched and downloaded online by itself. This dataset contains 150 samples divided into 3 categories: Iris Setosa (mountain iris), Iris Versicolour (chromatic iris) and Iris Virginica (virginian iris). Each sample has 4 characteristics: sepal length, sepal width, petal length, and petal width. Below we perform a KNN query (based on l2_distance) on the Iris dataset to determine the type of sample by identifying the K samples that most closely resemble a particular sample based on its characteristics.

  1. Create Iris tables and import data

    Prepare a table named iris_table and the corresponding Iris dataset data. The dataset has 150 rows of data, each row consisting of a four-dimensional eigenvector and species.

    CREATE TABLE iris_table(
        species varchar(100), -- category
        attributes vecf64(4) -- feature
    ); 
    LOAD DATA INFILE '/your_path/iris.csv' INTO TABLE iris_table;
    
  2. Use KNN to predict the category of this input feature

    mysql>  select * from iris_table order by l2_distance(attributes,"[4,3.3,3,0.9]") asc limit 1;
    +------------------+--------------------+
    | species          | attributes         |
    +------------------+--------------------+
    | Iris-versicolour | [4.9, 2.4, 3.3, 1] |
    +------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>  select * from iris_table order by l2_distance(attributes,"[4,3.3,3,0.9]") asc limit 5;
    +------------------+----------------------+
    | species          | attributes           |
    +------------------+----------------------+
    | Iris-versicolour | [4.9, 2.4, 3.3, 1]   |
    | Iris-versicolour | [5.1, 2.5, 3, 1.1]   |
    | Iris-versicolour | [5, 2.3, 3.3, 1]     |
    | Iris-setosa      | [4.8, 3.4, 1.9, 0.2] |
    | Iris-versicolour | [5.2, 2.7, 3.9, 1.4] |
    +------------------+----------------------+
    5 rows in set (0.00 sec)
    

After searching, we can roughly determine that the sample type is discolored Iris.

To understand the role of vector retrieval in building RAG applications, refer to the RAG Application Foundation example in the View Application Development Example.

Reference Documents

Vector data type

L2_DISTANCE()