Filter with a WHERE clause in nearest neighbor queries - PolarDB

To improve the efficiency of nearest neighbor queries and narrow the search scope, you can use a WHERE clause and create an index on the filter column.

Exact indexes

You can create the following types of exact indexes on the filter column to enable fast, exact nearest neighbor search.

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

For queries involving multiple columns, create a multicolumn index.

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

Approximate indexes

Use approximate indexes unless a very low percentage of total rows are matched. For approximate indexes, filtering is performed after the index scan. For example, if the condition matches 10% of rows and an Hierarchical Navigable Small World (HNSW) index (default hnsw.ef_search value: 40) is used, the average result may include only four matched rows. If you need more results, you can increase the hnsw.ef_search value.

Using approximate indexes to execute queries with filter conditions may return fewer results. Starting from version 0.8.0, you can enable iterative index scans, which automatically scan more of the index as need or until the limits specified by hnsw.max_scan_tuples or ivfflat.max_probes are reached.

Check the extension version

Check the extension version.

SELECT * FROM pg_extension WHERE extname = 'vector';

The extversion field in the returned result displays the extension version. Sample result:

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

(Optional) If the extension version is earlier than 0.8.0, upgrade the extension.
```
ALTER EXTENSION vector UPDATE TO '0.8.0';
```

Iterative index scans

Supported sorting methods:

strict_order: precisely sorts the results by distance.
relaxed_order: allows results to be slightly out of distance-based ordering but provides better recall. You can use a materialized CTE to get strict ordering.

Examples

Enable iterative index scans.

Specify strict ordering.

-- HNSW index
SET hnsw.iterative_scan = strict_order;

-- IVFFlat index
SET ivfflat.iterative_scan = strict_order;

Specify relaxed ordering.

-- HNSW index
SET hnsw.iterative_scan = relaxed_order;

-- IVFFlat index
SET ivfflat.iterative_scan = relaxed_order;

In relaxed ordering mode, you can use a materialized CTE to get strictly sorted results.

Enable iterative index scan and specify relaxed ordering.

Use a materialized CTE to get strictly sorted results.

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;

When you use a materialized CTE for queries that filter by distance, place the distance filter outside of the CTE and other filters inside the CTE 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. Therefore, you can configure related parameters to control when a scan ends.

Index type	Parameter	Description
HNSW	`hnsw.max_scan_tuples`	The maximum number of tuples that can be scanned during a query. Default value: 20000.
HNSW	`hnsw.scan_mem_multiplier`	The maximum memory multiplier allowed during the execution of the HNSW algorithm. Default value: 1. It 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, you can increase this multiplier.
IVFFlat	`ivfflat.max_probes`	The maximum number of probes allowed during a query with an IVFFlat index. If this value is less than `ivfflat.probes`, `ivfflat.probes` is used.