Why pgvector Returns Fewer Rows When You Add a Filter

Your query has LIMIT 10 and comes back with 4 rows, or none, even though the table holds thousands of matches. Nothing is broken. With an approximate index, pgvector applies your WHERE clause after it scans the index. It finds a small set of near neighbours first and then throws away the ones that do not match.

The fix for most people, on pgvector 0.8.0 or later, is one line:

SET hnsw.iterative_scan = strict_order;

The rest of this note explains why this happens, and when another fix is better.

What is actually happening

An HNSW index does not visit every row. It walks a graph and keeps a short list of the best candidates, and the length of that list is hnsw.ef_search, which is 40 by default. Only then does Postgres apply your filter.

The pgvector documentation states the result directly: "If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average."

Your LIMIT has no say in this. It caps the result, but it cannot make the index look further. So the number of rows you get back depends on how selective the filter is:

Filter matches	Candidates scanned	Rows left on average
50% of rows	40	20
10% of rows	40	4
1% of rows	40	0.4
0.1% of rows	40	0.04

This is illustrative arithmetic based on the documentation's own example, and it assumes the filter is unrelated to similarity. Real data is messier. If the matching rows are clustered far from your query vector, you can get zero rows even from a filter that matches a lot of the table.

This also explains a confusing symptom: the same query works for a big customer and returns nothing for a small one. The small customer's rows are a tiny share of the table, so they rarely appear in the 40 candidates. IVFFlat has the same problem, controlled by ivfflat.probes, which defaults to 1.

The worse case is the one that looks fine. A query can return all 10 rows and still miss better matches, because the index never reached them. A row count check will not catch that. Only a recall check will, which we come to below.

First, confirm that this is your problem

Check your pgvector version, since iterative scans need 0.8.0 or later:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

Then run the query with EXPLAIN ANALYZE. If the plan shows an index scan on your vector index with a line like "Rows Removed by Filter", the filter ran after the scan, and this note applies.

Fix 1: iterative index scans

Iterative scans make the index keep going until enough rows pass the filter. The documentation says the scan continues "until enough results are found (or it reaches hnsw.max_scan_tuples or ivfflat.max_probes)".

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SELECT id FROM items
WHERE category_id = 123
ORDER BY embedding <-> '[1,2,3]'
LIMIT 10;
COMMIT;

There are two modes for HNSW:

Setting	Behaviour	Use when
strict_order	Results are in exact order by distance	You need exact ranking
relaxed_order	Results can be slightly out of order, with better recall	Recall matters more, or you re-rank anyway

IVFFlat supports relaxed ordering with ivfflat.iterative_scan = relaxed_order.

If you want the recall of relaxed mode and exact order as well, the documentation shows a materialized CTE:

WITH relaxed_results AS MATERIALIZED (
  SELECT id, embedding <-> '[1,2,3]' AS distance
  FROM items
  WHERE category_id = 123
  ORDER BY distance
  LIMIT 10
)
SELECT * FROM relaxed_results ORDER BY distance + 0;

The + 0 is needed on Postgres 17 and later, according to the documentation.

Know the limits. The scan stops at hnsw.max_scan_tuples, 20,000 by default, and at a memory limit set by hnsw.scan_mem_multiplier, a multiple of work_mem. A very selective filter can hit the cap and still return too few rows. It also makes the query slower, because it is doing more work. If you need more, raise the cap and measure latency. For IVFFlat, the equivalent cap is ivfflat.max_probes.

Fix 2: raise ef_search

On a version without iterative scans, or for a modest filter, raise the candidate list:

SET hnsw.ef_search = 200;

This is a blunt tool. It helps a filter that matches 20 percent of rows, but it cannot rescue one that matches 0.1 percent, and every query pays for the larger list, filtered or not. Set it per transaction with SET LOCAL rather than globally.

Fix 3: an ordinary index on the filter column

If the filter is selective, you may not want the vector index at all. When only a few thousand rows match, Postgres can find them with a normal index and compare the query vector with each one exactly. That is often fast, and recall is perfect.

CREATE INDEX ON items (category_id);

For filtering on metadata in a JSONB column, index the expression you query:

CREATE INDEX ON items ((metadata->>'tenant_id'));

SELECT id FROM items
WHERE metadata->>'tenant_id' = 'acme'
ORDER BY embedding <-> '[1,2,3]'
LIMIT 10;

The expression in the query has to match the expression in the index. Run ANALYZE so the planner has accurate statistics, and check with EXPLAIN which index it chose. This is the same logic as you may not need a vector database: exact search over a small set is often all you need.

Fix 4: partial indexes

If you filter on a few distinct values, build a separate approximate index for each:

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops)
WHERE (category_id = 123);

Every candidate in that index already matches, so nothing is thrown away after the scan. The cost is one index per value, so this suits a handful of categories rather than thousands of customers.

Fix 5: partitioning

If you filter on many different values, such as one tenant per customer, the documentation suggests partitioning:

CREATE TABLE items (embedding vector(3), category_id int)
PARTITION BY LIST(category_id);

Each partition gets its own index, and a filter on the partition key only searches the right one. This is the heaviest change, because it alters the table, but it also isolates tenants cleanly. That helps with the permission problem we covered in securing an AI application: retrieval has to respect who is asking.

Settings that quietly do not apply

A common surprise is setting hnsw.iterative_scan or hnsw.ef_search, seeing no change, and concluding the fix does not work. These are session settings, so they only affect the connection that set them.

With a connection pool, a plain SET on one connection does nothing for the next query if that query lands on a different connection. In a transaction-mode pooler, a plain SET can also stay on a server connection and affect another request. The safe pattern is the one used above: wrap the query in a transaction and use SET LOCAL, so the setting lasts for exactly that query.

The same applies if you query through a framework. A wrapper such as a LangChain vector store still runs an ordinary SQL query with your metadata filter in the WHERE clause, so everything in this note applies to it. You may need to run the settings on the same connection or transaction, or set them for the database role:

ALTER ROLE app_user SET hnsw.iterative_scan = 'strict_order';

Which fix to use

Your situation	Start with
pgvector 0.8.0 or later, ordinary filter	Iterative scans
Older pgvector, filter matches a large share of rows	Raise hnsw.ef_search, then plan an upgrade
Filter leaves a few thousand rows or fewer	Ordinary index and exact search
A few fixed categories	Partial indexes
Many tenants	Partitioning

These can be combined. A common setup is an ordinary index for small tenants, with iterative scans for large ones, and the planner choosing between them.

Check recall, not just row count

Getting 10 rows back does not mean they are the right 10. To measure it, compare the approximate result with an exact one. The documentation's approach is to turn index scans off for one transaction:

BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items
WHERE category_id = 123
ORDER BY embedding <-> '[1,2,3]'
LIMIT 10;
COMMIT;

Run a few hundred real queries both ways, and count how many exact results also appeared in the approximate ones. Do it for your most selective filters, not just typical ones, since that is where recall falls. A retrieval system that quietly returns second-best passages gives no error, and it shows up later as a high similarity score on the wrong passage. Add those queries to your evaluation set so a later change cannot bring the problem back.

What we do not know

We have not benchmarked these options. The right choice depends on your row counts, how your filter relates to your embeddings, your hardware and your latency budget, and there is no universal threshold at which exact search stops being the better choice. Settings and defaults also change between pgvector releases, so check the README for your version before relying on any number here.