Querying & Filtering

Nearest-neighbor operators, distance calculations, aggregates, WHERE filtering with iterative index scans, and hybrid search in pgvector.

Distance Operators

pgvector supports six distance operators for ordering (ORDER BY) and threshold filtering:

Operator	Metric	Supported Types	Notes
`<->`	L2 (Euclidean) distance	`vector`, `halfvec`, `sparsevec`	$\sqrt{\sum (a_i - b_i)^2}$
`<#>`	Negative inner product	`vector`, `halfvec`, `sparsevec`	Returns $-\sum a_i b_i$ because PostgreSQL index scans only support `ASC` order
`<=>`	Cosine distance	`vector`, `halfvec`, `sparsevec`	$1 - \frac{a \cdot b}{\\|a\\|\\|b\\|}$ (range `[0, 2]`)
`<+>`	L1 (Taxicab / Manhattan) distance	`vector`, `halfvec`, `sparsevec`	(\sum
`<~>`	Hamming distance	`bit`	Number of differing bits (`XOR` popcount)
`<%>`	Jaccard distance	`bit`	(1 - \frac{

Normalized Vectors

If your vectors are already normalized to unit length (such as OpenAI text-embedding-3-* or BGE embeddings), prefer inner product (<#>) over cosine distance (<=>) for the best CPU throughput.

Nearest-Neighbor Queries

-- Top 5 nearest neighbors by L2 distance
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

-- Top 5 nearest neighbors by cosine distance
SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- Top 5 nearest neighbors by maximum inner product
SELECT * FROM items ORDER BY embedding <#> '[3,1,2]' LIMIT 5;

Calculating Distances & Similarity Scores

Retrieve the distance alongside each row:

-- L2 distance
SELECT id, embedding <-> '[3,1,2]' AS distance FROM items;

-- Actual inner product (multiply negative inner product by -1)
SELECT id, (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;

-- Cosine similarity (1 - cosine distance)
SELECT id, 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;

Distance Threshold Queries

Get all rows within a given distance threshold:

SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;

Combine with ORDER BY and LIMIT to use an HNSW or IVFFlat index:

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

Native Range / Sphere Queries in vchord

If you frequently query by distance radius, vchord provides a dedicated <<->> sphere operator (WHERE embedding <<->> sphere('[3,1,2]'::vector, 5)) that evaluates range predicates directly inside the vchordrq or vchordg index without requiring ORDER BY ... LIMIT. See Similarity Filter in vchord.

Aggregates

Compute the mean (AVG) or component-wise sum (SUM) of vectors:

SELECT AVG(embedding) FROM items;

SELECT category_id, AVG(embedding) AS centroid
FROM items
GROUP BY category_id;

Filtering (`WHERE` Clauses) & Iterative Index Scans

Combine vector search with relational predicates:

SELECT * FROM items WHERE category_id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Indexing Filter Columns

Standard B-tree index on the filter column (good when category_id = 123 matches a very small fraction of rows):
```
CREATE INDEX ON items (category_id);
```

Partial vector index (ideal when you only filter by a few known values):

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);

Partitioning (ideal when filtering across a bounded set of high-volume tenants or time ranges):

CREATE TABLE items (embedding vector(3), category_id int) PARTITION BY LIST(category_id);

Iterative Index Scans (`pgvector 0.8.0+`)

By default, approximate vector index scans examine a fixed number of candidates (hnsw.ef_search or ivfflat.probes). If a WHERE filter rejects most of those candidates, the query may return fewer rows than LIMIT.

Starting in pgvector 0.8.0, enable iterative index scans so the index automatically continues scanning until enough matching rows are found (or max_scan_tuples / max_probes is reached):

-- HNSW iterative scan
SET hnsw.iterative_scan = relaxed_order;
-- Or strict_order to guarantee exact distance ordering across iterations:
-- SET hnsw.iterative_scan = strict_order;

-- IVFFlat iterative scan
SET ivfflat.iterative_scan = relaxed_order;

When using relaxed_order, results may be slightly out of order by distance; wrap the query in a CTE or subquery to re-sort the final LIMIT rows:

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

Controlling Iterative Scan Budgets

-- Maximum tuples HNSW will visit during an iterative scan (default: 20000)
SET hnsw.max_scan_tuples = 20000;

-- Maximum memory multiplier (multiple of work_mem) HNSW uses for visited state (default: 1)
SET hnsw.scan_mem_multiplier = 2;

-- Maximum probes IVFFlat will visit during an iterative scan
SET ivfflat.max_probes = 100;

Hybrid Search

You can combine pgvector semantic similarity search with lexical full-text search using Reciprocal Rank Fusion (RRF).

Hybrid Search with PostgreSQL `tsvector`

SELECT id, content FROM (
    SELECT id, content, 1.0 / (60 + RANK() OVER (ORDER BY embedding <=> '[3,1,2]')) AS score
    FROM items
    ORDER BY embedding <=> '[3,1,2]' LIMIT 20
    UNION ALL
    SELECT id, content, 1.0 / (60 + RANK() OVER (ORDER BY ts_rank_cd(textsearch, query) DESC)) AS score
    FROM items, plainto_tsquery('english', 'hello search') query
    WHERE textsearch @@ query
    ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 20
) searches
GROUP BY id, content
ORDER BY SUM(score) DESC
LIMIT 5;

Hybrid Search with `vchord_bm25` or `pg_bestmatch`

For true Okapi BM25 ranking with document-length normalization and corpus IDF:

Pair pgvector (or vchord) with vchord_bm25 and pg_tokenizer — see the Hybrid Search Guide.
Or generate BM25-weighted sparsevec vectors directly inside PostgreSQL using pg_bestmatch and index them with pgvector HNSW (sparsevec_ip_ops).