pgvector Filtered Search Returns Too Few Results: Fixes

A pgvector query asks for the 10 nearest documents for one tenant, and gets back three, or none, even though the tenant has thousands of documents. Nothing errors. The cause is how approximate indexes and SQL filters interact: the HNSW index finds a fixed number of nearest candidates first, and PostgreSQL applies the WHERE clause afterwards. This guide reproduces the problem and measures each fix.

The lab used pgvector 0.8.7 on PostgreSQL 17 with 300,000 generated 64-dimension vectors in 500 clusters. 50 tenants each own about 6,000 rows (2% of the table). Results were compared with exact search. These are lab numbers from synthetic data, not a client system; real embeddings will give different recall values.

Reproduce it

CREATE INDEX docs_embedding_hnsw ON docs USING hnsw (embedding vector_l2_ops);
CREATE INDEX docs_tenant_idx ON docs (tenant_id);

SELECT id
FROM docs
WHERE tenant_id = 123
ORDER BY embedding <-> $1
LIMIT 10;

Without the filter, the same query vector returned 10 of the 10 true nearest neighbours. With the filter, it returned 0 rows. The plan shows why:

Limit (actual rows=0 loops=1)
  ->  Index Scan using docs_embedding_hnsw on docs (actual rows=0 loops=1)
        Order By: (embedding <-> $1)
        Filter: (tenant_id = 123)
        Rows Removed by Filter: 40

HNSW returned its default 40 candidates (hnsw.ef_search = 40). All 40 belonged to other tenants, because tenant 123 had no documents near this query vector. That is common in multi-tenant search: the globally nearest documents belong to whichever tenants write about the same topic. Raising hnsw.ef_search to 400 still returned 0 rows here; a bigger candidate pool does not help when none of the near candidates match.

The planner does not always pick HNSW. For a tenant with 300 rows, an earlier run of the lab on uniformly random vectors chose the B-tree on tenant_id and an exact top-N sort, which is correct. The failure appears for mid-sized filters, where the planner expects the vector index to find enough matches.

Measured fixes

Timings are single executions on a warm cache and are only comparable within this table. "True top 10 found" is recall@10 against exact search for the same tenant.

Fix 1: iterative index scans (pgvector 0.8.0+)

Iterative scans let the index keep searching when the filter removes too many candidates, until it has enough rows or reaches a limit:

SET hnsw.iterative_scan = relaxed_order;   -- or strict_order
SET hnsw.max_scan_tuples = 20000;          -- default: stop after this many tuples
SET hnsw.scan_mem_multiplier = 1;          -- default: memory limit as a multiple of work_mem

strict_order keeps results in exact distance order; relaxed_order allows slight disorder and, per the pgvector documentation, gives better recall. In the lab, relaxed order with default settings found 8 of the true 10. In an earlier run on uniformly random vectors, lowering hnsw.max_scan_tuples to 1,000 cut recall from 7 to 2, so the limit is a recall and latency trade-off to measure, not a value to copy.

With relaxed order, re-sort the final rows. On PostgreSQL 17 and later, the outer ORDER BY needs + 0; without it, the lab plan showed no Sort node at all, so results could stay out of order:

WITH relaxed AS MATERIALIZED (
  SELECT id, embedding <-> $1 AS distance
  FROM docs
  WHERE tenant_id = 123
  ORDER BY distance
  LIMIT 10
)
SELECT id, distance
FROM relaxed
ORDER BY distance + 0;

Set these per transaction with SET LOCAL in the query path that needs them, so other vector queries keep their tested behavior. IVFFlat has the equivalent ivfflat.iterative_scan and ivfflat.max_probes.

Fix 2: exact search when the filter is selective

For tenant 123, exact search over its 6,000 rows through the B-tree took 1.22 ms and returned the true top 10. Approximate search is only worth its recall loss when the filtered set is too large to scan. If most tenants are small, exact search for them is simpler and correct. The planner usually chooses it for very small filters; for mid-sized ones, test both, and if the exact path is consistently better, steer the query, for example with a separate code path for tenant-scoped search.

Fix 3: partial indexes or partitions for large filter values

A partial HNSW index for one large tenant returned the exact top 10 in 0.09 ms from a 3.3 MB index:

CREATE INDEX CONCURRENTLY docs_t123_hnsw
  ON docs USING hnsw (embedding vector_l2_ops)
  WHERE tenant_id = 123;

pgvector's guidance is to use partial indexes when filtering by a few distinct values, and partitioning when filtering by many. Partial indexes suit a handful of very large tenants or categories; hundreds of them become an operational burden. List or hash partitioning by tenant gives each partition its own smaller index, at the cost of partition management and cross-tenant queries.

How to detect it in production

• Count results. Log how often a vector query returns fewer rows than its LIMIT when the filter should have enough matches. That rate is the cheapest early signal.
• Measure recall per filter. On a replica, compare the approximate result with an exact one for a sample of queries and filter values. Run the exact version with SET enable_indexscan = off in a session, and report recall for small, medium and large tenants separately.
• Read the plan. Rows Removed by Filter close to hnsw.ef_search, with fewer output rows than the limit, is the signature of this problem.
• Check the extension version with SELECT extversion FROM pg_extension WHERE extname = 'vector';. Iterative scans need 0.8.0 or later; managed services offer specific versions per engine release.

Choosing a fix

• Risk: iterative scans increase latency for filters that match little; partial indexes add build time, memory and write cost per index; exact search grows linearly with tenant size.
• Rollback: iterative settings are per session or transaction; partial indexes drop with DROP INDEX CONCURRENTLY.
• Validation: re-measure recall and latency for each tenant size band, and the rate of short result sets, before and after.

For index choice beyond filtering, see HNSW vs IVFFlat and the semantic search API guide. The pgvector performance review covers recall, plans and resource use on your own data.