Home / AI & GenAI / Vector Databases

AI & GenAITroubleshootingAdvanced

Sev1: pgvector search went from 50 ms to seconds — HNSW index not used, filters and ef_search

RAG retrieval on pgvector suddenly takes seconds and the assistant times out. Use EXPLAIN to see whether the HNSW index is used, fix operator and query shape, handle filtered search and restore latency.

Deepak Bhardwaj3 min read

Applies toPostgreSQL 15-17 with pgvector 0.7/0.8 (self-managed, Azure Database for PostgreSQL, Amazon RDS/Aurora)

Symptoms

  • Vector search latency jumps from tens of milliseconds to seconds; chat answers time out.
  • Database CPU high, many sessions running the similarity query.
  • Started after a data load, a query change or a pgvector/PostgreSQL upgrade.

1. Check the plan

Run the real query with a real embedding:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content
FROM chunks
WHERE tenant_id = 42
ORDER BY embedding <=> '[0.012, -0.034, ...]'::vector
LIMIT 10;
Plan shows Meaning
Index Scan using chunks_embedding_hnsw_idx Index used; look at filters and ef_search
Seq Scan on chunks + Sort Index not used; exact search over all rows
Index scan, then many rows removed by filter, few results Filtered search problem

2. Index not used: common causes

Operator doesn't match the index's operator class. An index built with vector_cosine_ops is used only by <=>; vector_l2_ops by <->; vector_ip_ops by <#>.

SELECT indexdef FROM pg_indexes WHERE tablename = 'chunks';

Change the query operator to match, or build the matching index.

No ORDER BY ... LIMIT. The index is only used for nearest-neighbour ordering with a limit. Queries like WHERE embedding <=> x < 0.3 without ordering do a full scan.

Index missing or invalid after a failed CREATE INDEX CONCURRENTLY:

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

Drop and rebuild invalid indexes.

Casting or dimension mismatch: the query vector cast to a different type (halfvec vs vector) or dimension than the indexed expression.

With a selective filter (one tenant out of thousands), HNSW finds the nearest 40 (default hnsw.ef_search) vectors, then the filter removes most of them; you get too few results, and the planner may switch to a sequential scan.

Options:

-- pgvector 0.8+: keep scanning the index until enough rows pass the filter
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.ef_search = 100;
  • Partial indexes per large tenant or category:
CREATE INDEX CONCURRENTLY chunks_t42_hnsw ON chunks USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 42;
  • Partitioning by tenant (each partition with its own HNSW index) for many-tenant setups.
  • A B-tree index on tenant_id lets the planner choose an exact search inside small tenants, which is fast when the tenant has few rows.

4. Index fits in memory?

HNSW performance drops sharply when the index doesn't fit in RAM.

SELECT pg_size_pretty(pg_relation_size('chunks_embedding_hnsw_idx'));
SHOW shared_buffers;

If the index has grown past available memory, options are more RAM, halfvec (half the size), fewer dimensions, or splitting data.

5. Immediate relief

  • Fix the query shape (operator, ORDER BY/LIMIT) and deploy; this is usually the whole fix.
  • Lower hnsw.ef_search if it was raised to a very high value globally.
  • Add a statement timeout for retrieval so slow searches don't pile up:
ALTER ROLE rag_app SET statement_timeout = '2s';

Warning

Building a new HNSW index on a large table takes a long time and lots of memory. Use CREATE INDEX CONCURRENTLY, raise maintenance_work_mem for that session, and run it off-peak.

Prevent it next time

  • Keep a latency test with real filters in CI for retrieval queries; check plans with EXPLAIN.
  • Alert on p95 retrieval latency and on sequential scans of the vector table (pg_stat_user_tables.seq_scan).
  • Plan memory for index growth.
  • Record the operator class next to the query in code.

Related fixes