The incident looks like an application bug: the support assistant asks for ten documents, but pgvector returns three. The table has thousands of matching rows for the tenant. The embedding query works without the tenant filter. The failure is the interaction between approximate nearest-neighbor search and SQL filtering.

Short Version

Approximate pgvector indexes do not always search the whole table before filters are applied. With HNSW or IVFFlat, PostgreSQL can retrieve a candidate set by vector distance and then apply the WHERE clause. If the filter is selective, too few candidates survive.

The fixes are workload-specific: raise hnsw.ef_search, use iterative scans where supported, add exact indexes on filter columns, create partial vector indexes for low-cardinality filters, partition by high-impact filter keys, and verify every change with EXPLAIN ANALYZE.

Situation

Teams add tenant and category filters to vector queries as the application matures from prototype to multi-tenant production, and recall quietly degrades without any error being raised.

The Problem

SQL developers expect this mental model:

  1. Apply WHERE tenant_id = 42.
  2. Rank the remaining rows by vector distance.
  3. Return the top ten.

Approximate vector indexes often behave closer to this model:

  1. Walk the vector index until enough candidate vectors are found.
  2. Apply WHERE tenant_id = 42 to those candidates.
  3. Return whatever remains.

That difference matters when filters are selective. If a tenant owns 5 percent of the table, the first 40 HNSW candidates may only contain a few rows from that tenant. The query asks for ten, but the index path did not search enough of the graph to find ten authorized matches.

The pgvector README documents this filtered-search behavior and notes that iterative index scans can scan more of the index until enough rows are found or scan limits are reached. hnsw.ef_search defaults to 40 with a valid range of 1–1000; the default is tuned for unfiltered recall and is frequently too low once a selective WHERE clause is added. Confirm the deployed version’s default before assuming 40, since self-managed forks and managed Postgres providers occasionally override extension defaults. The question that matters operationally: does the production query path always include a selective filter, and if so, has anyone measured how many candidates survive it?

How It Works

flowchart TD
    Query[tenant filtered vector query] --> ANN[approximate index scan]
    ANN --> Candidates[small vector candidate set]
    Candidates --> Filter[apply tenant filter]
    Filter --> Few[too few rows survive]
    Query --> Tune[increase search effort]
    Tune --> More[larger candidate set]
    More --> Filter2[apply tenant filter]
    Filter2 --> Enough[enough rows returned]

A typical failing query:

SELECT id, body
FROM document_chunks
WHERE tenant_id = $1
  AND category = $2
ORDER BY embedding <=> $3
LIMIT 10;

The problem is not the syntax. The problem is that tenant_id and category change the selectivity of the vector search. A query that works globally can under-return inside one tenant or category.

Architecture and Operating Model

Treat filtered vector search as a query-planning problem, not an LLM problem.

Step 1: Capture the failing query shape. Keep the tenant, category, limit, vector operator, and index type. Do not debug with a global query if production always includes tenant filters.

Step 2: Run EXPLAIN ANALYZE. Confirm whether the plan uses HNSW, IVFFlat, a sequential scan, or an exact index on filter columns. Look for rows removed by filters and the actual row count returned.

Step 3: Tune candidate search. For HNSW, raise hnsw.ef_search for the query or transaction. Higher values improve recall but cost latency. Use SET LOCAL so one sensitive endpoint does not change the whole session pool.

BEGIN;
SET LOCAL hnsw.ef_search = 200;

SELECT id, body
FROM document_chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

COMMIT;

Step 4: Use iterative scans where available. pgvector added hnsw.iterative_scan (and the IVFFlat equivalent) in version 0.8.0, released October 2024. It defaults to off. Confirm the deployed extension is 0.8.0 or later before relying on it — earlier versions do not have this parameter and will error on the SET.

SET hnsw.iterative_scan = strict_order;

Strict ordering preserves distance order. Relaxed ordering can improve recall but may need a materialized CTE and final ordering step.

Step 5: Change physical design when tuning is not enough. If a filter is always present, make it part of the storage strategy. For a few categories, use partial vector indexes. For many tenants or categories with strong isolation, consider partitioning.

CREATE INDEX document_chunks_policy_hnsw
ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WHERE category = 'policy';

CREATE TABLE document_chunks_partitioned (
  tenant_id bigint NOT NULL,
  document_id bigint NOT NULL,
  embedding vector(1536),
  body text NOT NULL
) PARTITION BY HASH (tenant_id);

In Practice

The DBA instinct is useful here: tune from the plan, not from vibes.

Filtered vector search should have a small library of known query shapes with expected plans. A tenant-scoped RAG endpoint, a category-scoped search endpoint, and an admin global search endpoint should not share one tuning setting blindly.

Platform teams should expose safe query classes rather than letting every application build arbitrary vector SQL. A retrieval service can choose ef_search, timeouts, result limits, and fallback behavior by route.

Where It Breaks

Failure modeTriggerFix
Under-return after tenant filterCandidate set is too small for selective tenantRaise ef_search or enable iterative scans
Slow query after raising ef_searchSearch effort grows for every requestUse route-specific settings and timeouts
Partial index explosionOne partial vector index per tenantUse partitions or collection boundaries instead
Partition fanoutQuery touches too many partitionsPartition only by filters that are always present
Exact scan beats ANNFilter matches a tiny row setUse B-tree filter index then exact distance order

Security, Cost, Observability, and Failure Notes

Security requires filtering before results leave the database. Over-fetching globally and filtering in the application can leak unauthorized chunk text into logs, traces, or prompt assembly.

Cost rises when search effort is raised globally. ef_search = 400 may solve recall for one tenant and burn CPU for every other query. Tune per endpoint.

Observability should track requested LIMIT, returned row count, empty-result rate, tenant selectivity, query latency, index used, and configured search effort. Alert on systematic under-return, not just errors.

Failure handling should include a fallback: if approximate search returns too few rows, rerun with higher search effort or exact search over a bounded filtered candidate set. The fallback should be explicit and measured.

Decision Checklist

  • Does the production query always include tenant or category filters?
  • What percentage of rows survive each filter?
  • Does EXPLAIN ANALYZE show an approximate index scan?
  • How many rows are removed by the filter?
  • Does increasing ef_search improve returned row count?
  • Is iterative scan available in the deployed pgvector version?
  • Would an exact B-tree filter plus distance sort be faster for selective filters?
  • Are partial indexes limited to a small number of stable values?
  • Would partitioning match the real query boundary?
  • Is under-return measured as a retrieval-quality metric?

What to Do Next

Problem: Selective tenant or category filters silently under-return results from approximate pgvector indexes, and the failure looks like an application bug rather than a query-planning issue.

Solution: Tune from the plan, not from vibes — raise hnsw.ef_search per route with SET LOCAL, enable hnsw.iterative_scan on pgvector 0.8.0+, and move to partial indexes or partitioning when a filter is always present.

Proof: EXPLAIN ANALYZE on the production-shaped, filtered query shows rows removed by the filter and a returned row count that matches the requested LIMIT.

Action: This week, run EXPLAIN ANALYZE on the most tenant-selective production query, record the candidate count before and after the filter, and confirm the deployed pgvector version supports hnsw.iterative_scan before recommending it to the team.

Sources to Verify