Filtering

Check the extension version

Run the following query to check the installed version:

SELECT * FROM pg_extension WHERE extname = 'vector';

The extversion field shows the current version. Sample 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';

Sorting modes

Iterative index scans support two sorting modes:

• `strict_order`: results are sorted precisely by distance.

• `relaxed_order`: results may be slightly out of distance-based order, but recall is better. Use a materialized CTE to enforce strict ordering when needed.

Enable iterative index scans

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;

Get strict ordering in relaxed mode

In relaxed_order mode, use a materialized CTE to sort the final results precisely by distance:

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;

Filter by distance

For queries that filter by distance, use a materialized CTE and place the distance filter *outside* the CTE. Put all other filters *inside* the CTE:

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;

This placement gives the best performance due to how the PostgreSQL executor processes the query.