AnalyticDB:Perform vector search

Exact search

The exact search method is a brute-force search method that compares the distance or similarity between all vectors. This method results in low response speeds but can achieve a 100% recall rate.

You can perform exact search based on the Euclidean distance, inner product distance, or cosine similarity.

• Euclidean distance

  SELECT ID, l2_distance(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) as score FROM <TABLE_NAME> ORDER BY l2_squared_distance(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) LIMIT <TOPK>;

• Inner product distance

  SELECT ID, inner_product_distance(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) as score FROM <TABLE_NAME> ORDER BY negative_inner_product_distance(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) LIMIT <TOPK>;

• Cosine similarity

  SELECT ID, cosine_similarity(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) as score FROM <TABLE_NAME> ORDER BY 
  cosine_distance(<VECTOR_COLUMN_NAME>, array[1,2,3...N]::float4[]) LIMIT <TOPK>;

Parameters:

• score: the Euclidean distance, inner product distance, or cosine similarity. Vectors are sorted by Euclidean distance in ascending order, or by inner product distance or cosine similarity in descending order.

• <VECTOR_COLUMN_NAME>: the name of the vector column.

• <TABLE_NAME>: the name of the vector table.

• <TOPK>: the number of top result sets.

Approximate index search

The approximate index search method uses HNSW indexes to search data. This method works fast but returns approximate results at a recall rate of more than 99%.

SELECT id, chunk, intime, url FROM chunks
ORDER BY
    feature <=> array[10,2.0,..., 1536.0]::real[]
LIMIT 100;

QUERY PLAN
-------------------------------------------------
 Limit
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Merge Key: ((feature <=> $0))
          ->  Limit
               ->  Ann Index Scan using feature_idx on chunks
                Order By: (feature <=> $0)
 Optimizer: Postgres query optimizer

SELECT id, chunk, intime, url FROM chunks WHERE
    intime > '2023-04-01' AND intime <= '2023-05-01'
ORDER BY
    feature <=> array[10,2.0,..., 1536.0]::real[]
LIMIT 100;

References

SQL optimization for vector search

This section describes SQL optimization for vector search. After you create a vector table, you can create multiple vector indexes for a vector column based on your business requirements. The SQL statement and the involved index must match. For example, the <-> operator can work with only indexes that are created by using the Euclidean distance, the <#> operator can work with only indexes that are created by using the inner product distance, and the <=> operator can work with only indexes that are created by using the cosine similarity.

CREATE TABLE test_table (
    id serial primary key,
    feature real[]
) distributed by (id);

CREATE INDEX idx_test_table_feature_l2 ON test_table USING ann(feature) WITH (dim=768, distancemeasure=l2, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_test_table_feature_ip ON test_table USING ann(feature) WITH (dim=768, distancemeasure=ip, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_test_table_feature_cosine ON test_table USING ann(feature) WITH (dim=768, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

• If you do not want to return the score value of the vector distance, you can execute the following SQL statements to query vector data:

  -- Search vector data based on the Euclidean distance. 
  SELECT id 
  FROM test_table
  ORDER BY feature <-> array[1,2,3 ... 768]::real[]
  LIMIT topk;
  
  -- Search vector data based on the inner product distance. 
  SELECT id
  FROM test_table
  ORDER BY feature <#> array[1,2,3 ... 768]::real[]
  LIMIT topk;
  
  -- Search vector data based on the cosine similarity. 
  SELECT id
  FROM test_table
  ORDER BY feature <=> array[1,2,3 ... 768]::real[]
  LIMIT topk;

• If you want to return the score value of the vector distance, you can use the sort result of the vector index to calculate the actual score value of the vector distance. This avoids a complete vector distance calculation process and reduces the amount of time required for calculation. Sample statements:

  -- Search vector data based on the Euclidean distance. 
  SELECT t.id as id, sqrt(t.score) as score 
  FROM 
    (SELECT id, feature <-> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t;
  
  -- Search vector data based on the inner product distance. 
  SELECT t.id as id, (-1 * t.score) as score
  FROM
    (SELECT id, feature <#> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t;
  
  -- Search vector data based on the cosine similarity. 
  SELECT t.id as id, (1.0 - t.score) as score
  FROM 
    (SELECT id, feature <=> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t;

• If you want to return results that are filtered based on a score range, you can execute the following SQL statements to query vector data. The statements use the sort result of the vector index to calculate the final score value of the vector distance. This reduces the amount of time required for calculation.

  -- Search vector data based on the Euclidean distance. 
  SELECT t.id as id, sqrt(t.score) as score 
  FROM 
    (SELECT id, feature <-> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t
  WHERE score < 100;
  
  -- Search vector data based on the inner product distance. 
  SELECT t.id as id, (-1 * t.score) as score
  FROM
    (SELECT id, feature <#> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t
  WHERE score > 10;
  
  -- Search vector data based on the cosine similarity. 
  SELECT t.id as id, (1.0 - t.score) as score
  FROM 
    (SELECT id, feature <=> array[1,2,3 ... 768]::real[] as score
     FROM test_table
     ORDER BY score LIMIT topk) t
  WHERE score > 0.5;

Engine parameters related to vector search

You can configure the preceding parameters for sessions.