Create approximate indexes - PolarDB - Alibaba Cloud Documentation Center

By default, pgvector performs exact nearest neighbor searches that provide perfect recall. To increase search speed, you can perform approximate nearest neighbor searches by adding indexes, which may involve a tradeoff in recall. Supported approximate index types include HNSW and IVFFlat.

HNSW

A Hierarchical Navigable Small Worlds (HNSW) index creates a multilayer graph and provides better query performance than an Inverted File with Flat Compression (IVFFlat) index in terms of the speed-recall tradeoff. However, an HNSW index has a longer build time and higher memory usage. Unlike IVFFlat, HNSW does not require training and can be created even if the table contains no data.

Create an HNSW index

Create an HNSW index for each distance function you want to use.

Euclidean distance (L2)

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

Note

Use a distance function based on the vector type. For example, use the halfvec_l2_ops function for the halfvec vector type and the sparsevec_l2_ops function for the sparsevec vector type.

Inner product

CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);

Cosine distance

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

Manhattan distance (L1)

CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);

Hamming distance

CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);

Jaccard distance

CREATE INDEX ON items USING hnsw (embedding bit_jaccard_ops);

Supported vector types:

Vector: vectors with up to 2,000 dimensions.
Halfvec: half-precision vectors with up to 4,000 dimensions.
Sparsevec: sparse vectors with up to 1,000 non-zero elements.
Bit: binary vectors with up to 64,000 dimensions.

Index creation options

Parameter	Description
`m`	The maximum number of connections per layer. Default value: 16.
`ef_construction`	The size of the dynamic candidate list used to construct the graph. Default value: 64. A higher value improves recall but slows down indexing.

Example

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

Query options

Parameter	Description
`hnsw.ef_search`	The size of the dynamic candidate list used during search. Default value: 40. A higher value improves recall but slows down the query.

Examples

Specify the hnsw.ef_search parameter for the current session.
```
SET hnsw.ef_search = 100;
```
Specify the hnsw.ef_search in a transaction for a single query by using SET LOCAL.
```
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;
```

IVFFlat

IVFFlat indexes combine the benefits of inverted indexes and vector search. An IVFFlat index divides vectors into lists and searches a subset of those lists closest to the query vector. It provides faster build and lower memory usage than HNSW, but has lower query performance in terms of speed-recall tradeoff.

To achieve good recall, follow these guidelines:

Create the index after the table has data
Choose an appropriate number of lists. Start with rows/1000 for datasets up to 1 million rows and use sqrt(rows) datasets larger than 1 million rows.
Specify an appropriate number of probes during queries. The use of more probes improves recall but may slow down the queries. We recommend using sqrt(lists).

Create an IVFFlat index

Create an IVFFlat index for each distance function you want to use.

Euclidean distance (L2)

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

Note

The distance calculation operation varies based on the vector type. For example, use the halfvec_l2_ops operation for the halfvec vector type and the sparsevec_l2_ops operation for the sparsevec vector type.

Inner product

CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);

Cosine distance

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Hamming distance

CREATE INDEX ON items USING ivfflat (embedding bit_hamming_ops) WITH (lists = 100);

Supported vector types:

Vector: vectors with up to 2,000 dimensions.
Halfvec: half-precision vectors with up to 4,000 dimensions.
Bit: binary vectors with up to 64,000 dimensions.

Query options

Parameter	Description
`ivfflat.probes`	The number of probes. Default value: 1. A higher value improves recall but slows down the query. You can set the number of probes equal to the number of lists to enable an exact nearest neighbor search, but in this case, the planner will not use the index.

Example

Specify the ivfflat.probes parameter in a transaction for a single query by using SET LOCAL.

BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT ...
COMMIT;

Speed up index building

The index building speed depends on the following parameters.

Parameter

Description

maintenance_work_mem

The maximum amount of memory allowed for index building. Adjust the parameter based on the cluster specifications. If the graph fits into maintenance_work_mem, the index building speed is faster. Otherwise, the system displays:

NOTICE: hnsw graph no longer fits into maintenance_work_mem after 100000 tuples
DETAIL: Building takes significantly more time.
HINT: Increase maintenance_work_mem to speed up building.

Note

Specify an appropriate value to prevent memory exhaustion of the server.

max_parallel_maintenance_workers

The number of parallel workers. Default value: 2. You can speed up index creation by using more parallel workers.

SET max_parallel_maintenance_workers = 7; -- plus leader thread

Note

To use more workers, increase max_parallel_workers (to 8 by default).

Like other index types, it is faster to create an index after loading your initial data. The index options also significantly impact the building time.

Monitor the index building progress

You can monitor the index building progress by querying the pg_stat_progress_create_index view.

HNSW index building phases:
1. initializing
2. loading tuples
IVFFlat index building phases:
1. initializing
2. performing k-means
3. assigning tuples
4. loading tuples

--- HNSW
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

--- IVFFlat
SELECT phase, round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

Note

The % field is updated only during the loading tuples phase.