Filter with a WHERE clause in nearest neighbor queries-PolarDB(PolarDB)-阿里云帮助中心

Nearest neighbor queries without filtering scan the entire vector index, which is slow and returns irrelevant results when only a subset of rows applies. Adding a WHERE clause and indexing the filter column narrows the search scope and improves query performance.

Exact indexes

Create an index on the filter column to enable fast, exact nearest neighbor search. The following index types are supported:

B-tree (default)
Hash
GiST
SP-GiST
GIN
BRIN

For queries that filter on multiple columns, create a multicolumn index.

Exact indexes are suitable for conditions that match a low percentage of rows.

Approximate indexes

With approximate indexes, filtering is applied after the index scan. If the filter matches 10% of rows and an HNSW index is used with the default hnsw.ef_search of 40, only four rows match on average. To get more results, increase the hnsw.ef_search value.

Use approximate indexes when a large percentage of rows match the filter condition. For a very low match percentage, exact indexes perform better.

Iterative index scans

Starting from version 0.8.0, iterative index scans automatically scan more of the index until enough matching results are found or until the limits set by hnsw.max_scan_tuples or ivfflat.max_probes are reached.

Check your extension version

SELECT * FROM pg_extension WHERE extname = 'vector';

Check the extversion field in the result. Example output:

  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 17941 | vector  |    17865 |         2200 | t              | 0.8.0      |           |
(1 row)

If the version is earlier than 0.8.0, upgrade the extension:

ALTER EXTENSION vector UPDATE TO '0.8.0';

Enable iterative index scans

Two sort ordering modes are available:

strict_order: sorts results precisely by distance.
relaxed_order: allows results to be slightly out of distance order for better recall. Use a materialized CTE to re-sort the results strictly after retrieval.

Strict ordering

-- HNSW index
SET hnsw.iterative_scan = strict_order;

-- IVFFlat index
SET ivfflat.iterative_scan = strict_order;

Relaxed ordering

-- HNSW index
SET hnsw.iterative_scan = relaxed_order;

-- IVFFlat index
SET ivfflat.iterative_scan = relaxed_order;

To get strictly sorted results in relaxed_order mode, wrap the query in a materialized CTE and re-sort outside it:

WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items WHERE id = 1 ORDER BY distance LIMIT 5
) SELECT * FROM relaxed_results ORDER BY distance;

For queries that also filter by distance, place the distance filter outside the CTE and other filters inside for best performance:

WITH nearest_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance
    FROM items
    ORDER BY distance
    LIMIT 5
) SELECT * FROM nearest_results WHERE distance < 5 ORDER BY distance;

Iterative index scan parameters

Scanning a larger portion of an approximate index is costly. Use the following parameters to control when a scan stops.

Index type	Parameter	Default	Description
HNSW	`hnsw.max_scan_tuples`	20,000	Maximum number of tuples to scan during a query.
HNSW	`hnsw.scan_mem_multiplier`	1	Maximum memory multiplier allowed during the execution of the HNSW algorithm. Works with `hnsw.max_scan_tuples` to determine the maximum memory allowed during candidate scanning. If increasing `hnsw.max_scan_tuples` does not improve recall, increase this value instead.
IVFFlat	`ivfflat.max_probes`	—	Maximum number of probes during a query. If this value is less than `ivfflat.probes`, `ivfflat.probes` is used.