pgvector HNSW Filtering: My RAG Asked for 10 Rows and Got 0

The support ticket was one line long: "Your assistant says our docs don't mention refunds. We have an entire page called Refunds."

I checked. The page was there. It was chunked, embedded, and sitting in Postgres with the right tenant_id. I ran the exact retrieval query the bot runs, by hand, in psql.

Zero rows.

Not wrong rows. Not bad rows. Zero, from a query with LIMIT 10, against a tenant with 4,000 chunks. The cause is pgvector HNSW filtering: your WHERE clause runs after the approximate index has already picked its candidates. Small tenants lose that race, and nothing in your logs tells you.

TL;DR

• pgvector's HNSW index finds the hnsw.ef_search nearest vectors (default 40) across the whole table first, then Postgres applies your WHERE filter to those 40.
• Expected rows returned ≈ ef_search × selectivity. A filter that matches 2% of rows gives you about 0.8 rows on average, no matter what your LIMIT says.
• Any filter matching less than LIMIT / ef_search of the table (10/40 = 25% for top-10) returns fewer rows than you asked for, on average.
• Fix it with iterative index scans (SET hnsw.iterative_scan = strict_order, pgvector 0.8.0+), partial indexes or partitions per tenant, or an exact scan for small filters.
• Detect it by logging every retrieval that returns fewer rows than LIMIT. It's free and it would have caught my bug on day one.

What does the broken query look like?

This is the most normal RAG query in the world:

SELECT id, content
FROM chunks
WHERE tenant_id = 42
ORDER BY embedding <=> $1   -- cosine distance
LIMIT 10;

With an HNSW index on embedding:

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

My table had about 200,000 chunks. One big tenant owned most of them. Tenant 42 owned 4,000, which is 2%. For the big tenant, retrieval was perfect. For tenant 42, the bot was confidently telling a paying customer their own documentation didn't exist.

Here's what EXPLAIN ANALYZE showed:

Limit  (actual rows=0)
  ->  Index Scan using chunks_embedding_idx on chunks  (actual rows=0)
        Order By: (embedding <=> '[...]'::vector)
        Filter: (tenant_id = 42)
        Rows Removed by Filter: 40

Rows Removed by Filter: 40. That number is the whole story.

Why does pgvector HNSW filtering return fewer rows than LIMIT?

Because HNSW is an approximate index that only produces a fixed-size candidate list, and Postgres filters that list after the fact. The index has no idea tenant_id exists.

HNSW search walks a graph of vectors, greedily hopping toward your query. It keeps a running list of the best candidates it has seen. The size of that list is hnsw.ef_search, default 40. When the walk finishes, the index hands those 40 rows to Postgres, in distance order.

Then the executor applies WHERE tenant_id = 42. Every row from another tenant gets thrown away. If none of the 40 belong to tenant 42, you get nothing back, and the query still "succeeds."

The math is brutal and simple. If tenant 42's chunks were sprinkled randomly through the vector space:

• Expected matches = 40 × 0.02 = 0.8 rows
• Probability of zero matches = 0.98^40 ≈ 45%

So roughly half of tenant 42's questions would retrieve nothing at all, and almost none would retrieve a full 10. In reality it's often worse. My big tenant sold a similar product, so its refund-policy chunks sat right next to tenant 42's refund-policy chunks in embedding space. The 40 nearest neighbors to "how do refunds work" were all from the big tenant. Semantic similarity was actively working against me.

At what filter selectivity does HNSW start dropping rows?

As soon as your filter matches less than LIMIT / ef_search of the table. For LIMIT 10 and the default ef_search = 40, that threshold is 25%.

That 10% row isn't something I made up. The pgvector README spells out the same example: a condition matching 10% of rows with default ef_search returns about 4 rows on average.

This is why the bug hides so well. Your test tenant is probably your biggest tenant, or your only tenant. Your filter is probably selective only for the customers who complain the least loudly.

Does raising hnsw.ef_search fix it?

Partially. It's a bandaid, and you should know what it costs before you reach for it.

SET hnsw.ef_search = 400;

Now you get 400 candidates instead of 40, and 2% selectivity gives you about 8 expected rows. Still under 10. Search cost grows with ef_search on every query, including the ones that never needed it, and the max is 1000. At 0.1% selectivity, even 1000 isn't enough. You're tuning a global knob to fix a per-filter problem.

How do iterative index scans fix pgvector filtering?

They let the index keep walking the graph until it has enough rows that survive your filter. This is the real fix if you're on pgvector 0.8.0 or later.

SET hnsw.iterative_scan = strict_order;   -- or relaxed_order
SET hnsw.max_scan_tuples = 20000;         -- default; safety cap

Instead of "find 40, filter, done," the scan becomes "find 40, filter, still short? keep going." It stops when LIMIT is satisfied or it hits hnsw.max_scan_tuples.

Two modes:

• strict_order returns results exactly sorted by distance.
• relaxed_order is faster but results can come back slightly out of order. Re-sort them yourself with a materialized CTE:

WITH candidates AS MATERIALIZED (
  SELECT id, content, embedding <=> $1 AS distance
  FROM chunks
  WHERE tenant_id = 42
  ORDER BY distance
  LIMIT 10
)
SELECT * FROM candidates ORDER BY distance;

The catch is max_scan_tuples. For a tiny tenant buried in a huge table, the scan can hit that cap before it finds 10 matches, and you're back to short results. Iterative scans move the cliff much further out. They don't delete it.

I set SET LOCAL hnsw.iterative_scan = relaxed_order inside the retrieval transaction rather than globally, so other vector queries keep their old behavior until I've checked them.

What's the best fix for multi-tenant RAG on pgvector?

Stop making small tenants share a candidate list with big ones. You have three options, and they stack.

1. Partial indexes, if the filter has few distinct values. One HNSW index per tenant or category, each scoped by WHERE:

CREATE INDEX chunks_t42_hnsw ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WHERE (tenant_id = 42);

The planner uses it when your query's WHERE matches the index predicate. Every candidate is already the right tenant, so filtering removes nothing. This doesn't scale to 10,000 tenants, but it's great for a handful of big categories.

2. Partitioning, if the filter has many values. PARTITION BY LIST (tenant_id) or hash partitioning, with an HNSW index per partition. Postgres prunes to the right partition and searches only that graph.

3. Exact search for small filters. For tenant 42, an exact scan over 4,000 vectors is cheap, and exact means 100% recall. Force it by materializing the filtered set first so the HNSW index can't be used for the ordering:

WITH tenant_chunks AS MATERIALIZED (
  SELECT id, content, embedding
  FROM chunks
  WHERE tenant_id = 42          -- uses a B-tree on tenant_id
)
SELECT id, content
FROM tenant_chunks
ORDER BY embedding <=> $1
LIMIT 10;

What I shipped: iterative scans turned on for retrieval, plus this exact-search path for any tenant under a row-count threshold. Big tenants stay on the approximate index. Small tenants get perfect recall and still answer fast.

How do I detect HNSW filtering bugs before users do?

Log the shortfall. This is ten lines and it's the most valuable thing in this post:

rows = cur.execute(RETRIEVAL_SQL, (tenant_id, query_vec)).fetchall()
if len(rows) < k:
    log.warning(
        "retrieval_shortfall",
        extra={"tenant_id": tenant_id, "wanted": k, "got": len(rows)},
    )

A short result is fine if the tenant truly has fewer than k chunks. It's a bug signal if they have thousands. Once a week, also run a recall check: take a sample of real queries, run them with the index and with the exact CTE above, and compare the ID sets. If the overlap for filtered queries is far below the overlap for unfiltered ones, you've found this exact problem.

The part that still bugs me is how quiet it was. No error, no timeout, no slow query alert. The LLM got an empty context, did its best, and politely told a customer their Refunds page didn't exist.

So why did my pgvector query return 0 rows?

pgvector HNSW filtering is post-filtering: the index returns its hnsw.ef_search nearest neighbors (40 by default) from the whole table, and only then does Postgres apply your WHERE clause. When the filter matches a small slice of the table, like one tenant with 2% of the rows, most or all of those 40 candidates get discarded, so a LIMIT 10 query can return 0 to 3 rows with no error. Fix it by enabling hnsw.iterative_scan on pgvector 0.8.0+, giving selective filters their own partial index or partition, or routing small filtered sets to an exact scan, and log every query that returns fewer rows than LIMIT so the next one doesn't reach your users first.

Written by the developer behind Preterview, an interview prep platform.