E-MapReduce:Vector search on internal tables in a storage-compute separation architecture

Enable the vector index capability

If the vector index capability is not yet enabled on the instance, ask a user with administrative privileges to run the following statement.

ADMIN SET FRONTEND CONFIG ("enable_experimental_vector" = "true");

This statement is a dynamic configuration. Whether the instance allows you to modify this configuration directly, and whether the change persists, depends on the console capabilities of the instance.

Create an HNSW index

The following example creates an HNSW index that uses cosine similarity when the table is created. The dimension in the example is for demonstration only. The actual dim must match the output dimension of your Embedding model.

CREATE TABLE item_embeddings (
      id        BIGINT NOT NULL,
      category  VARCHAR(64),
      content   VARCHAR(65533),
      embedding ARRAY<FLOAT> NOT NULL,
      INDEX idx_embedding (embedding) USING VECTOR (
          "index_type" = "hnsw",
          "dim" = "4",
          "metric_type" = "cosine_similarity",
          "is_vector_normed" = "false",
          "M" = "16",
          "efconstruction" = "40"
      )
  ) ENGINE=OLAP
  DUPLICATE KEY(id)
  DISTRIBUTED BY HASH(id) BUCKETS 1;

The parameters are described in the following table.

Create an IVFPQ index

The following example creates an IVFPQ index that uses L2 distance when the table is created.

CREATE TABLE item_embeddings_ivfpq (
      id        BIGINT NOT NULL,
      embedding ARRAY<FLOAT> NOT NULL,
      INDEX idx_embedding_ivfpq (embedding) USING VECTOR (
          "index_type" = "ivfpq",
          "dim" = "4",
          "metric_type" = "l2_distance",
          "is_vector_normed" = "false",
          "nlist" = "16",
          "M_IVFPQ" = "2",
          "nbits" = "8"
      )
  ) ENGINE=OLAP
  DUPLICATE KEY(id)
  DISTRIBUTED BY HASH(id) BUCKETS 1;

In addition to index_type, dim, metric_type, and is_vector_normed, the IVFPQ-specific parameters are described in the following table.

Add an index to an existing table

For an existing internal table, run CREATE INDEX to add a vector index.

CREATE INDEX idx_embedding
  ON item_embeddings (embedding)
  USING VECTOR (
      "index_type" = "hnsw",
      "dim" = "4",
      "metric_type" = "cosine_similarity",
      "is_vector_normed" = "false",
      "M" = "16",
      "efconstruction" = "40"
  );

Adding an index triggers an asynchronous Schema Change. Run the following statement to confirm that the task is complete before you start performance testing.

SHOW ALTER TABLE COLUMN
  WHERE TableName = 'item_embeddings'
  ORDER BY CreateTime DESC
  LIMIT 1;

A task state of FINISHED indicates that the index change is complete.

TopK approximate search

A larger cosine similarity means more similar, so use descending order.

SELECT
      id,
      content,
      approx_cosine_similarity(
          embedding,
          CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
      ) AS score
  FROM item_embeddings
  ORDER BY score DESC
  LIMIT 10;

A smaller L2 distance means more similar, so use ascending order.

SELECT
      id,
      approx_l2_distance(
          embedding,
          CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
      ) AS distance
  FROM item_embeddings_ivfpq
  ORDER BY distance ASC
  LIMIT 10;

To hit the vector index, a TopK query must meet all of the following conditions.

• The query uses an approximate function that matches the metric_type of the index.

• One argument of the function is the indexed vector column, and the other is a constant float array that can be determined at the optimization stage.

• ORDER BY contains only one vector-score expression.

• L2 distance uses ASC, and cosine similarity uses DESC.

• The query specifies LIMIT.

Combine scalar and vector search

You can first use business fields to narrow the search scope, and then return the vector TopK results.

SELECT
      id,
      content,
      approx_cosine_similarity(
          embedding,
          CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
      ) AS score
  FROM item_embeddings
  WHERE category = 'document'
  ORDER BY score DESC
  LIMIT 10;

This statement runs successfully, but an ordinary scalar predicate may prevent the query from meeting the rewrite conditions for a TopK vector index, in which case the query falls back to row-by-row distance computation. Do not infer from the shape of the SQL statement whether filtering happens before or after the vector search. Before you use the query in production, confirm the actual scan and filter behavior with EXPLAIN and the Query Profile.

Range search

Range search must use the same approximate function as the sort expression. The following example returns only the results whose cosine similarity is not lower than the threshold.

SELECT *
  FROM (
      SELECT
          id,
          content,
          approx_cosine_similarity(
              embedding,
              CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
          ) AS score
      FROM item_embeddings
  ) t
  WHERE score >= 0.80
  ORDER BY score DESC
  LIMIT 10;

For cosine similarity, use a "greater than or equal to threshold" range condition. For L2 distance, use a "less than or equal to threshold" range condition.

The query must still include LIMIT, and the number of returned rows does not exceed the specified TopK value. This usage therefore adds a threshold filter on top of a TopK search. It is not equivalent to an RNN query that returns all rows within the threshold range.

Exact search

A distance function without the approx_ prefix computes the distance row by row and does not use the vector index. Exact search is suitable for queries on small datasets, and can also serve as the ground truth for ANN recall.

SELECT
      id,
      cosine_similarity(
          embedding,
          CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
      ) AS score
  FROM item_embeddings
  ORDER BY score DESC
  LIMIT 10;

Adjust query parameters

Query parameters are set through the session variable ann_params. Each connection in the connection pool must set this variable separately. We recommend that you configure it uniformly in the connection initialization SQL.

HNSW controls the search width with efSearch.

SET ann_params = '{"efSearch": 100}';

A larger efSearch searches more candidate nodes and may improve recall, but it also increases query CPU consumption and latency.

The common search parameters for IVFPQ are as follows.

SET ann_params = '{
    "nprobe": 16,
    "max_codes": 0,
    "scan_table_threshold": 0,
    "polysemous_ht": 0,
    "range_search_confidence": 0.1
  }';

We recommend that you use a representative query set to evaluate QPS, P95/P99 latency, and Recall@K together, instead of choosing parameters based on the time of a single query. Adjust one parameter at a time and measure again. If you change several parameters at once, you cannot tell which one delivers the gain.

Check whether the index takes effect

Run EXPLAIN to view the execution plan.

EXPLAIN
  SELECT id
  FROM item_embeddings
  ORDER BY approx_cosine_similarity(
      embedding,
      CAST('[0.10, 0.30, 0.60, 0.75]' AS ARRAY<FLOAT>)
  ) DESC
  LIMIT 10;

If the following content appears in OlapScanNode, the optimizer has generated a vector index query plan.

VECTORINDEX: ON

In the Query Profile, focus on the following metrics.

The first query may include the time for shared storage reads and index loading. During performance evaluation, record the cold-cache results and the warmed-up results separately.

EXPLAIN shows that the vector index is not used. How do I troubleshoot?

Check the following items in order.

1. Whether enable_experimental_vector is enabled on the FE.

2. Whether the index build is complete, that is, whether the task state in SHOW ALTER TABLE COLUMN is FINISHED.

3. Whether the query function matches the metric_type of the index.

4. Whether L2 distance is sorted in ascending order and cosine similarity is sorted in descending order.

5. Whether ORDER BY contains only one vector-score expression and a LIMIT is present.

6. Whether the query vector is a constant float array whose dimension matches dim.

7. Whether the queried column is the vector column on which the index is created.

8. Whether the query contains operations that change the plan shape, such as ordinary scalar predicates, joins, aggregation, or nested subqueries. These operations may prevent the query from meeting the rewrite conditions for the vector index.

A vector index plan is generated, but the query is still slow. What do I do?

• Check VectorIndexLoadTime and the cache hit metrics to determine whether the slowness is caused by cold-cache loading.

• Check whether there are many small Segments. Too many Segments amplify index loading and merge overhead. You can finish Compaction in a test environment first and then compare.

• Check whether the number of rows in a Segment is lower than index_build_threshold. A Segment without an index falls back to row-by-row computation.

• Lower the HNSW efSearch or the IVFPQ nprobe, and then re-verify Recall@K.

• Check whether the query returns unnecessary large fields. The overhead of table lookups and network transfer can mask the actual time of the vector search.

The index build fails or takes a long time. What do I do?

• Confirm that the vector data has no dimension mismatch, null values, or invalid float numbers.

• Confirm that the IVFPQ dim is divisible by M_IVFPQ, and that the amount of training data is sufficient to support nlist.

• Check the remaining memory of the compute nodes and the index build concurrency. Increasing build concurrency raises both CPU and memory peaks.

• After a large-batch load, finish Compaction first to reduce the number of Segments and the subsequent search amplification. Whether to run it depends on your business window.