pgvector similarity search ignores the HNSW index: operator and opclass mismatch

Our documentation assistant retrieves the top 8 chunks from a doc_chunks table with about 1.4 million rows before asking the model to answer. After the table passed a million rows, retrieval p95 climbed to 1.9 seconds and the database CPU sat near 80 percent during office hours. At the same time, the support team reported that answers sometimes cited long, generic pages instead of the obviously relevant short one.

We had an HNSW index, so a full scan should not have been possible. EXPLAIN said otherwise:

Limit  (cost=171842.20..171842.22 rows=8 width=72) (actual time=1874.311..1874.316 rows=8 loops=1)
  ->  Sort  (cost=171842.20..175342.20 rows=1400000 width=72) (actual time=1874.309..1874.312 rows=8 loops=1)
        Sort Key: ((embedding <-> '[0.0123,-0.0456,...]'::vector))
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Seq Scan on doc_chunks  (cost=0.00..136842.20 rows=1400000 width=72) (actual time=0.031..1611.902 rows=1400000 loops=1)
Execution Time: 1874.402 ms

Sequential scan and a sort over all 1.4 million rows.

Reproducing it

Running the same query with a different operator flipped the plan immediately:

EXPLAIN ANALYZE
SELECT id FROM doc_chunks ORDER BY embedding <=> $1 LIMIT 8;
-- Index Scan using doc_chunks_embedding_idx ... Execution Time: 13.982 ms

What we ruled out

• Statistics or planner costs. ANALYZE changed nothing, and SET enable_seqscan = off still could not use the index with <->.
• The index being invalid. pg_index.indisvalid was true, and the cosine query used it.
• Too few rows for the index to be worth it. Not at 1.4 million.

Root cause

The index was created with vector_cosine_ops, which supports only the cosine distance operator <=>. Our ORM helper generated <->, which is Euclidean (L2) distance. An index built for one distance cannot answer a query ordered by another, so Postgres had no choice but to scan. The odd rankings had a related cause: our embeddings were not normalised, and L2 distance on unnormalised vectors is affected by vector length, which in our case correlated with chunk length.

What is happening under the hood

pgvector has one operator per distance function and one index operator class per operator:

An approximate index (HNSW or IVFFlat) is only used for ORDER BY embedding <op> constant LIMIT n when <op> belongs to the opclass the index was built with. It is a strict match, the same way a B-tree on lower(email) does not help a query on email.

On normalised vectors (length 1), cosine distance, inner product and L2 produce the same ranking. Most embedding providers already return normalised vectors, but not all models do, and some pipelines change vectors after the fact, for example by averaging chunk embeddings. Our pipeline averaged sub-chunk embeddings for long sections without re-normalising, so long sections had shorter vectors and ranked strangely under L2.

Note that <#> returns the negative inner product, so ascending order puts the best match first. Forgetting the sign is a classic way to get the worst results first.

Environment

Minimal reproduction

CREATE TABLE t (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO t (embedding)
SELECT ARRAY[random(), random(), random()]::vector FROM generate_series(1, 200000);
CREATE INDEX ON t USING hnsw (embedding vector_cosine_ops);
ANALYZE t;

EXPLAIN SELECT id FROM t ORDER BY embedding <-> '[1,0,0]' LIMIT 5;  -- Seq Scan
EXPLAIN SELECT id FROM t ORDER BY embedding <=> '[1,0,0]' LIMIT 5;  -- Index Scan

Who is affected

Anyone who builds the index and the query in different places: migrations written by one person and a helper written by another, or an ORM method name that hides the operator, such as l2_distance versus cosine_distance in pgvector-python.

The fix

We decided cosine was the right metric for our model and made everything agree with it.

- stmt = select(DocChunk).order_by(DocChunk.embedding.l2_distance(query_vec)).limit(8)
+ stmt = select(DocChunk).order_by(DocChunk.embedding.cosine_distance(query_vec)).limit(8)

We also normalised vectors before writing them, so that any future switch to inner product would give identical rankings:

import numpy as np

def normalise(v: np.ndarray) -> np.ndarray:
    n = np.linalg.norm(v)
    if n == 0:
        raise ValueError("zero vector")
    return v / n

The averaged sections were re-embedded with normalisation in a background job, about 90,000 rows.

Finally, the metric now lives in one place. The migration and the query helper both read the same constant, and a startup check compares it with the index definition:

SELECT indexdef FROM pg_indexes
WHERE tablename = 'doc_chunks' AND indexname = 'doc_chunks_embedding_idx';
-- must contain 'vector_cosine_ops'

Alternatives and trade-offs

• Rebuild the index with vector_l2_ops to match the existing query. Fine on normalised data, but we wanted cosine semantics regardless of vector length.
• Inner product (vector_ip_ops) is slightly cheaper to compute and equivalent on normalised vectors. We may switch later; the normalisation step makes that safe.
• Two indexes for two metrics. Doubles memory and write cost. Only worth it if you really query with both.

How to test the fix

def test_similarity_query_uses_index(db):
    plan = db.execute(text("EXPLAIN " + compiled_search_sql(dim=1536))).scalars().all()
    assert any("doc_chunks_embedding_idx" in line for line in plan)

For ranking quality we used our retrieval eval set of 300 real questions with known relevant chunks. Recall at 8 went from 0.71 to 0.84 after the operator and normalisation fixes.

Confirming in production

• Retrieval p95 dropped from 1.9 seconds to 21 ms.
• Database CPU during office hours fell from about 80 percent to 25 percent.
• pg_stat_user_indexes.idx_scan for the HNSW index started increasing with traffic, where before it was almost flat.

Regression guard

• The plan test above runs against a seeded Postgres in CI.
• The metric constant is shared by migration code and query code.
• A weekly job computes the mean vector norm of a sample of rows and alerts if it drifts away from 1.0.

Security implications

None in the operator fix itself. The incident did show that an expensive query path could be triggered by any user typing into the search box. We added a per-user rate limit on retrieval and a statement_timeout for the search role, so a future planner mistake cannot pin the database CPU.

Prevention checklist

• Pick one distance metric per embedding column and record it next to the schema.
• Use the operator that matches the index opclass, and test the plan.
• Normalise vectors if anything in your pipeline modifies them.
• Remember that <#> is negative inner product.
• Keep a retrieval eval set to catch ranking regressions.

References

• pgvector README, querying and indexing — https://github.com/pgvector/pgvector
• pgvector-python — https://github.com/pgvector/pgvector-python
• PostgreSQL EXPLAIN documentation — https://www.postgresql.org/docs/16/using-explain.html