pgvector query ignores the HNSW index: sequential scans caused by operator mismatches, ORDER BY expressions and parameter types

#Symptoms

• A top-k similarity query on a few million rows takes 1–20 seconds instead of milliseconds.
• CPU on the database jumps with every search request.
• EXPLAIN shows Seq Scan followed by Sort (or Top-N heapsort), not Index Scan using ..._hnsw.

#Step 1: get the real plan

Run the query exactly as the application sends it, with a real vector:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM doc_chunks
ORDER BY embedding <=> '[0.012, -0.034, ...]'
LIMIT 10;

A healthy plan looks like this:

Limit  (cost=... rows=10) (actual time=1.9..2.4 rows=10 loops=1)
  ->  Index Scan using doc_chunks_emb_hnsw on doc_chunks
        Order By: (embedding <=> '[...]'::vector)

If it shows Seq Scan, go through the causes below in order.

Tip

Applications often use prepared statements. Test with PREPARE q(vector) AS ...; EXPLAIN EXECUTE q('[...]'); to see the same plan the driver gets.

#Cause 1: operator doesn't match the index operator class

Check the index definition:

SELECT indexrelid::regclass, pg_get_indexdef(indexrelid)
FROM pg_index WHERE indrelid = 'doc_chunks'::regclass;

Fix: change the query operator, or build a second index with the operator class you query with. Don't mix metrics on one column without a reason.

#Cause 2: ORDER BY a computed similarity

This is the most common one:

-- Not indexable: orders by an expression of the distance, descending
SELECT id, 1 - (embedding <=> $1) AS score
FROM doc_chunks
ORDER BY score DESC
LIMIT 10;

The index can only return rows in ascending order of the raw distance operator. Compute the score in the select list but order by the operator:

SELECT id, 1 - (embedding <=> $1) AS score
FROM doc_chunks
ORDER BY embedding <=> $1
LIMIT 10;

Same for inner product: order by embedding <#> $1 ascending, display (embedding <#> $1) * -1.

#Cause 3: no LIMIT, or a LIMIT applied outside a subquery

The HNSW scan only makes sense with ORDER BY distance LIMIT k in the same query level. ORMs sometimes wrap the query in a subquery or apply the limit after a DISTINCT or GROUP BY. Check the SQL the ORM actually generates (log_min_duration_statement or pg_stat_statements) and move LIMIT next to the ORDER BY.

#Cause 4: the parameter isn't typed as vector

If the driver sends the query vector as text or numeric[], PostgreSQL may cast the column instead of the parameter, or pick a different operator. Cast the parameter explicitly:

ORDER BY embedding <=> $1::vector(1536)

In Python with psycopg, register the type once with pgvector.psycopg.register_vector(conn) so arrays are sent as vector.

For a halfvec expression index, both sides must use the same expression:

ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)

#Cause 5: a filter makes the planner prefer another path

With WHERE tenant_id = 42, the planner compares the HNSW scan (then filter) with a B-tree scan on tenant_id (then sort). If the tenant is small, the B-tree path is often correct and fast: an exact search over a few thousand rows. Problems start when the estimate is wrong:

EXPLAIN (ANALYZE) SELECT id FROM doc_chunks
WHERE tenant_id = 42 ORDER BY embedding <=> $1 LIMIT 10;
-- compare estimated rows vs actual rows on the filter node

-- Partial index for one very large tenant
CREATE INDEX CONCURRENTLY doc_chunks_emb_t42
  ON doc_chunks USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 42;

#Cause 6: the index is invalid

A CREATE INDEX CONCURRENTLY that failed or was cancelled leaves an invalid index the planner never uses:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index WHERE indrelid = 'doc_chunks'::regclass;

Drop it and build again (DROP INDEX CONCURRENTLY ...).

#Cause 7: the table is small or the cost settings push away from index scans

On a few thousand rows a sequential scan is fine. On large tables, check random_page_cost (should be about 1.1 on SSD storage, not 4) and that enable_indexscan isn't turned off by a session setting. To confirm the index can be used:

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN SELECT id FROM doc_chunks ORDER BY embedding <=> $1::vector(1536) LIMIT 10;
ROLLBACK;

If it still won't use the index with seqscan off, the query shape is the problem (causes 1–4). If it uses it, it's costing (causes 5 and 7).

#Quick reference

#Prevent it next time

• Keep the vector query in one place in the codebase and test its plan in CI with EXPLAIN (FORMAT JSON), asserting the index name appears.
• Always order by the raw distance operator ascending; compute scores in the select list.
• Register the pgvector type in every driver.
• Watch idx_scan for the vector index in pg_stat_user_indexes; if it stops growing while traffic continues, a deploy changed the query shape.