E-MapReduce:Vector search on DLF Paimon external tables

Enable distributed Global Index queries

Set the following session variables in every connection that runs a vector query.

SET paimon_global_index_scan_stage = 2;

The parameters are described in the following table.

Write the query vector

On the two-stage query path, the query vector must be a native array literal.

[0.12, 0.34, 0.56, 0.78]

Do not use the following form.

CAST('[0.12, 0.34, 0.56, 0.78]' AS ARRAY<FLOAT>)

In the serialization path of two-stage index expressions, CAST(string AS ARRAY<FLOAT>) can cause dimension validation to fail or prevent the Global Index from taking effect.

TopK vector search

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

SELECT
    item_id,
    approx_cosine_similarity(
        embedding,
        [0.12, 0.34, 0.56, 0.78]
    ) AS score
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
ORDER BY score DESC
LIMIT 10;

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

SELECT
    item_id,
    approx_l2_distance(
        embedding,
        [0.12, 0.34, 0.56, 0.78]
    ) AS distance
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
ORDER BY distance ASC
LIMIT 10;

A larger inner product means more similar, so sort in descending order.

SELECT
    item_id,
    approx_inner_product(
        embedding,
        [0.12, 0.34, 0.56, 0.78]
    ) AS score
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
ORDER BY score DESC
LIMIT 10;

To hit the TopK Global Index, a query must take the following shape.

• The score function is approx_l2_distance, approx_cosine_similarity, or approx_inner_product.

• One argument of the score function is the vector column that has a vector index, and the other is a constant float array.

• ORDER BY contains only one score expression.

• L2 distance uses ascending order, and cosine similarity and inner product use descending order.

Combine scalar and vector search

The following example filters by document type first, and then runs a cosine similarity TopK search.

SELECT
    item_id,
    title,
    approx_cosine_similarity(
        embedding,
        [0.12, 0.34, 0.56, 0.78]
    ) AS score
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
WHERE category = 'document'
ORDER BY score DESC
LIMIT 10;

A filter is applied at one of two points.

• Prefilter: the filter column has a compatible Paimon Bitmap or BTree Global Index, and the predicate shape supports pushdown. The system narrows the candidate set first and then runs the vector search, which helps preserve the recall of the TopK results after filtering.

• Postfilter: the filter cannot be pushed down to the Global Index, so it runs after vector recall. The filtered candidate set may contain fewer rows than LIMIT, and the final Recall@K may drop.

The following table lists the common conditions that support prefiltering.

Warm up the cache for the returned columns

After the vector index returns row IDs, StarRocks still reads the scalar columns in the query projection. If a query returns only item_id, warm up the row-scalar cache for that column.

SET query_timeout = 3600;

CACHE SELECT item_id
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
PROPERTIES (
    "cache_target" = "row_scalar",
    "verbose" = "true"
);

Some later branches may use rowid_scalar as the value of cache_target. If the server reports that the value is not supported, use the valid values listed in the error message.

If a query also returns large text or other columns, evaluate whether to warm up the whole table based on the actual projection.

CACHE SELECT *
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
PROPERTIES ("verbose" = "true");

Check whether the Global Index takes effect

First, confirm the session variables of the current connection.

SHOW VARIABLES LIKE 'paimon_global_index_scan_stage';
SHOW VARIABLES LIKE 'ann_params';

Then run EXPLAIN on the query.

EXPLAIN
SELECT item_id
FROM <DLF_CATALOG>.<DATABASE_NAME>.<PAIMON_TABLE>
ORDER BY approx_cosine_similarity(
    embedding,
    [0.12, 0.34, 0.56, 0.78]
) DESC
LIMIT 10;

Check whether the execution plan contains a Paimon Global Index scan stage followed by a data scan, instead of a plain full-table scan. Node names may differ between build versions, so also look at the index stage duration, the number of rows returned by the index, the table lookup volume, and the final scan row count in the Query Profile.

Verify recall

Compute the absolute Recall@K as follows.

Recall@K = |TopK IDs returned by ANN ∩ TopK IDs in the exact ground truth| / K

The ground truth data must come from the same data snapshot as the target Paimon table, and must use exactly the same ID mapping, vector model, and distance metric. If the IDs in the ground truth do not match the IDs in the external table, do not publish the absolute recall.

If no valid ground truth is available yet, use the results under high-quality parameters as the tuning baseline and compute the overlap ratio of the result sets. Report this metric only as relative consistency against a baseline, not as the product Recall@K.

A vector dimension mismatch is reported. How do I troubleshoot?

1. Confirm that the number of elements in the query array matches the index dimension.

2. Confirm that the query uses a native array literal and not CAST(string AS ARRAY<FLOAT>).

3. Confirm that the vector column on the external table and the vector index come from the same data and the same Embedding model.

4. Confirm that the array contains no invalid float values.

The Global Index does not take effect. How do I troubleshoot?

1. Confirm that the current connection has set paimon_global_index_scan_stage = 2.

2. Confirm that the Paimon table metadata contains a vector index.

3. Confirm that ORDER BY contains only one supported approximate score expression.

4. Confirm that the query vector is a constant native float array.

5. Run EXPLAIN on the query to rule out expression rewriting, extra sort columns, and unsupported filter shapes.

Fewer rows than LIMIT are returned after filtering. What do I do?

This usually means that some conditions can run only as a postfilter after vector recall. Handle it in the following ways.

• Create a compatible Paimon Bitmap or BTree Global Index on the filter column so that the condition becomes a prefilter.

• Verify Recall@K, query latency, and table lookup volume at the same time, so that fixing the result count does not create excessive resource overhead.

Why is the first query noticeably slower?

The first query may include remote reads of the index files, the metadata, and the data of the returned columns. Record the cold-cache results and the warmed-up results separately, and check the Data Cache, the row-scalar cache, and the shared storage access of the compute nodes.

QPS stops increasing after I raise concurrency. How do I troubleshoot?

• Check whether the CPU, memory, network, and object storage throughput of the compute nodes are saturated.

• Check whether parallel_number, the query concurrency, and the index shard concurrency together oversubscribe threads.

• Watch P95/P99 latency and the error count, not only the average QPS.

• Keep the query projection minimal and avoid returning unnecessary large fields.