AnalyticDB:Perform vector search

Verify index usage with EXPLAIN

Run EXPLAIN to confirm your query uses the vector index rather than a full table scan. Ann Index Scan in the output confirms the HNSW index is active.

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

Expected output when the index is active:

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

If you see Seq Scan instead of Ann Index Scan, the query is not using the vector index. Check that the operator matches the index's distance metric and that the index exists on the column.

Filter by metadata (hybrid search)

Combine vector similarity search with a metadata filter in the WHERE clause. The following example retrieves the top 100 most similar documents published within a specific date range:

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;

Match operators to indexes

A single vector column can have multiple indexes for different distance metrics. Each query operator works only with the index created using the corresponding distance metric. Mismatched operator-index pairs prevent index acceleration.

-- Create three indexes on the same column, one per distance metric
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);

Return results without a score

Use the <->, <#>, or <=> operators directly in the ORDER BY clause when you only need ranked IDs:

-- Euclidean distance
SELECT id
FROM test_table
ORDER BY feature <-> array[1,2,3...768]::real[]
LIMIT topk;

-- Inner product distance
SELECT id
FROM test_table
ORDER BY feature <#> array[1,2,3...768]::real[]
LIMIT topk;

-- Cosine similarity
SELECT id
FROM test_table
ORDER BY feature <=> array[1,2,3...768]::real[]
LIMIT topk;

Return results with a score

The index sort produces a raw distance value that differs from the actual metric value. Use a subquery to retrieve the raw score, then apply the conversion formula. This avoids a full distance recalculation.

-- Euclidean distance: raw score is squared L2; apply sqrt() to get L2 distance
SELECT t.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;

-- Inner product distance: raw score is negative; multiply by -1 to get inner product
SELECT t.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;

-- Cosine similarity: raw score is cosine distance; apply 1.0 - score to get similarity
SELECT t.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;

Filter by score range

Apply a WHERE filter on the converted score after the subquery sort and LIMIT. This uses the index for initial retrieval and computes the final score only for the top candidates.

-- Euclidean distance: return results with L2 distance below 100
SELECT t.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;

-- Inner product distance: return results with inner product above 10
SELECT t.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;

-- Cosine similarity: return results with similarity above 0.5
SELECT t.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;