Build Subvector Indexes to Accelerate ANN Recall in PolarDB - PolarDB

Subvector indexing builds an index on a subset of each vector's dimensions rather than the full vector. This reduces index size and speeds up approximate nearest neighbor (ANN) search, which helps effectively handle large-scale high-dimensional data. It works best with embedding models that support Matryoshka Representation Learning (MRL), where the leading dimensions carry enough semantic information for an initial search pass.

The typical workflow is two-stage: use the subvector index for a fast coarse search to retrieve a candidate set, then re-rank those candidates using full vectors to restore recall.

Index subvectors

Create a table with a vector column.

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

Create an expression index on the subvector using HNSW.
```
CREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 3)::vector(3)) vector_cosine_ops);
```
The expression subvector(embedding, 1, 3) extracts dimensions 1 through 3 from the embedding column. The result is cast to vector(3) to match the index operator class vector_cosine_ops.
Query the index. Find nearest neighbors by cosine distance:
```
SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 5;
```
Re-rank by full vectors to improve recall: The inner query uses the subvector index for a fast coarse search and retrieves 20 candidates. The outer query re-ranks those candidates using the full embedding vector and returns the top 5. Increase the inner LIMIT to trade query speed for higher recall.
```
SELECT * FROM (
    SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 20
) ORDER BY embedding <=> '[1,2,3,4,5]' LIMIT 5;
```