Boost PolarDB Vector Search Performance with HNSW Tuning - PolarDB - Alibaba Cloud - PolarDB

Vector search performance depends on matching the right index type and parameters to your workload. This topic covers how to optimize bulk loading, index configuration, query execution, vacuuming, and query monitoring for the vector database capabilities in PolarDB for PostgreSQL.

Decide between exact search and approximate search

Before tuning any parameters, choose the right search strategy for your workload:

Condition	Recommended approach
Small dataset or low query volume	Exact search (sequential scan) — no index overhead
100% recall required	Exact search — indexes trade recall for speed
High query throughput, acceptable recall reduction	Approximate search (index-based)

Recall measures the fraction of truly relevant results that a search returns. Higher recall means fewer results are missed; lower recall means the search is faster but may skip some matches.

Load data in bulk

Use COPY to load vectors efficiently:

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

Add indexes after loading the initial data. For a Python example, see bulk vector loading.

Build and manage indexes

Speed up index builds

For additional HNSW index creation options and parameter tuning, see and .

Create indexes without blocking writes

In production environments, always create indexes concurrently to avoid blocking write operations:

CREATE INDEX CONCURRENTLY ...

Debug query performance

Use EXPLAIN ANALYZE to verify which access method the planner uses:

EXPLAIN ANALYZE SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Check the output for Seq Scan (sequential scan, no index used) versus Index Scan (index is being used). If you expect an index scan but see a sequential scan, the planner may have determined that an index is not cost-effective for the current dataset size or query shape.

Optimize exact search

Parallelize sequential scans

For queries that cannot use an index, increase max_parallel_workers_per_gather to speed up aggregation and sorting across large datasets:

SET max_parallel_workers_per_gather = 4;

Use inner product for normalized vectors

If your vectors are normalized to unit length (L2 norm = 1), switch from L2 distance to inner product — it is faster and produces equivalent ranking:

SELECT * FROM items ORDER BY embedding <#> '[3,1,2]' LIMIT 5;

Tune approximate search

IVFFlat index parameters

Increasing the number of inverted lists (lists) reduces the search scope per query, improving speed at the cost of recall.

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

Vacuum HNSW indexes

Vacuuming HNSW indexes can take a long time. To speed up the process, rebuild the index first, then vacuum the table:

REINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;

Monitor query performance

Track slow queries with pg_stat_statements

Create the pg_stat_statements extension to track query-level performance statistics:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Important

Before creating this extension, add pg_stat_statements to shared_preload_libraries in the PolarDB console by modifying the shared_preload_libraries parameter. This change requires a cluster restart — plan the restart window to minimize disruption.

Find the top 20 most time-consuming queries:

SELECT query, calls,
    ROUND((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    ROUND((total_plan_time + total_exec_time) / 60000) AS total_time_min
FROM pg_stat_statements
ORDER BY total_plan_time + total_exec_time DESC
LIMIT 20;

For PolarDB for PostgreSQL 11, replace total_plan_time + total_exec_time with total_time.

Monitor recall

Compare approximate and exact search results to measure recall for your workload:

BEGIN;
SET LOCAL enable_indexscan = off; -- Force exact search
SELECT ...
COMMIT;

Run the same query with and without enable_indexscan = off, then compare result sets. The overlap between the two result sets indicates your current recall rate.