pgvector HNSW with a WHERE clause: you asked for 10 rows and got 3

What changed

This one is a default-value trap rather than a breaking announcement. pgvector’s own README states the mechanism without dressing it up: with an approximate index, filtering is applied after the index is scanned. The mitigation — iterative index scans — shipped in 0.8.0 and stayed opt-in. Six things define the window:

• Post-scan filtering is documented behaviour, not a bug. The README’s own example: a condition matching 10% of rows, with HNSW and the default hnsw.ef_search of 40, leaves “only 4 rows will match on average”. A LIMIT 10 therefore returns about 4 rows, silently.
• hnsw.ef_search is 40 by default, with a hard maximum of 1000. Both values are in the source, not just the docs: HNSW_DEFAULT_EF_SEARCH 40 and HNSW_MAX_EF_SEARCH 1000. Raising it trades latency for recall; it does not raise the 1000 ceiling.
• Iterative scans arrived in 0.8.0 (2024-10-30) and default to off. You have to set hnsw.iterative_scan to relaxed_order or strict_order, and the setting is session-scoped. Upgrading the extension changes nothing on its own.
• Iterative scans have their own stop conditions. hnsw.max_scan_tuples defaults to 20,000 and hnsw.scan_mem_multiplier to 1× work_mem. When the scan stops there, the query is still allowed to return fewer rows than the LIMIT.
• 2026 is the maintenance window, not the cause. 0.8.3 (2026-06-17) fixed “possible index corruption with HNSW vacuuming”; 0.8.4 (2026-06-30) fixed the hnsw graph not repaired error. 0.8.7 (2026-10-01) is the latest release at this review date, and the unreleased 0.9.0 entry in the changelog drops PostgreSQL 13 and 14.
• The index has a dimension wall. A vector column can be indexed with HNSW only up to 2,000 dimensions; halfvec raises that to 4,000 and bit to 64,000. A 3,072-dimension embedding fails at CREATE INDEX time, which is at least an honest error — unlike the row shortfall.

Detect: are you affected?

All five checks are read-only and use SQL only. Checks 1 and 2 tell you whether the defaults apply to your session; check 3 is the one that proves the row shortfall; check 4 explains which failure family you are in; check 5 is the cheap sanity check people skip.

Check 1 — extension and server version

SELECT extversion FROM pg_extension WHERE extname = 'vector';
SHOW server_version;

Check 2 — the three settings that decide the outcome

SHOW hnsw.ef_search;        -- 40 by default
SHOW hnsw.iterative_scan;   -- off by default
SHOW hnsw.max_scan_tuples;  -- 20000 by default

Check 3 — the count(*) comparison that proves it

Run this inside one transaction so SET LOCAL cannot leak into other callers. Replace the tenant value and the vector literal with real ones; the literal must have the same dimension as the column.

BEGIN;
SET LOCAL hnsw.iterative_scan = off;   -- reproduce the default explicitly

-- how many rows the filter alone matches (must be at least the LIMIT)
SELECT count(*) AS matching_rows FROM chunks WHERE tenant_id = 42;

-- how many rows the query actually returns
SELECT count(*) AS rows_returned
  FROM (
    SELECT id
      FROM chunks
     WHERE tenant_id = 42
     ORDER BY embedding <=> '[0.013,-0.041,0.087]'::vector
     LIMIT 10
  ) s;
ROLLBACK;

One caveat: wrapping the query in SELECT count(*) can change the plan, because the outer query no longer needs rows in distance order. Use check 3 to measure the row count and check 4 for the definitive plan.

Check 4 — EXPLAIN tells you which family you are in

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
  FROM chunks
 WHERE tenant_id = 42
 ORDER BY embedding <=> '[0.013,-0.041,0.087]'::vector
 LIMIT 10;

Check 5 — the cheap ones people skip

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
  FROM pg_stat_user_tables
 WHERE relname = 'chunks';

SELECT indexname, indexdef
  FROM pg_indexes
 WHERE tablename = 'chunks';

Not affected? If check 3 returns as many rows as the LIMIT, and check 4 shows either an exact plan on purpose or an HNSW scan without a post-filter, stop here and do not buy it. If you want a second opinion first, email [email protected] with the four outputs (version, settings, count(*) row, and the plan) and the pgvector version you are on.

Decision tree

not enough rows match the filter   -> stop, the query is answering honestly
rows match, query returns fewer
  + Index Scan + Filter            -> FM-01 (post-filter under-return)
  + iterative_scan = off           -> FM-02 (the setting never reached the query)
  + Bitmap Heap Scan + Sort        -> FM-03 (planner refused the HNSW index)
  + rows out of distance order     -> FM-04 (ordering semantics)
  + recall fell after upgrade/VACUUM -> FM-05 (rebuild the index)
CREATE INDEX fails on dimensions   -> FM-06 (2,000-dimension wall)

Failure modes

Six failures, each with the trigger, the observable evidence and the fix direction. Two are visible here.

Migration: what the paid chapter covers

The fix is almost never “turn the knob up”. It is a decision about where the filter belongs, made per caller, because a human debugging session and a pooled application connection do not see the same settings. The chapter splits the work like this:

The bundle also carries the decision tree that picks between “enable iterative scan”, “partial index”, “partition” and “exact plan”, a rollback that is one setting, and a 24-hour watch list: returned-rows histogram per query, Rows Removed by Filter on the retrieval query, and recall against a frozen sample set.

Appendix

• pgvector README — filtering applied after the index scan, the “4 rows” example, iterative index scans, max_scan_tuples 20,000, HNSW dimension caps (reviewed 2026-11-08)
• pgvector CHANGELOG — iterative scans in 0.8.0 (2024-10-30); HNSW vacuum fixes in 0.8.3 (2026-06-17) and 0.8.4 (2026-06-30); latest release 0.8.7 (2026-10-01) (reviewed 2026-11-08)
• pgvector source — HNSW_DEFAULT_EF_SEARCH 40, HNSW_MAX_EF_SEARCH 1000, 2,000-dimension HNSW cap and GUC defaults: iterative_scan = off, max_scan_tuples = 20000 (reviewed 2026-11-08)
• pgvector issue #1032 — planner refused HNSW behind an = ANY(...) filter plus an expression qual (2026-09-27) (reviewed 2026-11-08: the reporter’s measurements are on PG 17.11 / pgvector 0.8.6; the maintainer closed it the same day as not a pgvector bug — reason quoted in failure mode 03 of the paid chapter)
• pgvector issue #862 — relaxed_order, ordering and the PostgreSQL 17 materialized-CTE behaviour (opened 2025-06-20, closed 2025-07-02) (reviewed 2026-11-08)
• pgvector issue #912 — session-scope confusion around the scan settings (opened 2025-10-16, closed 2025-10-18) (reviewed 2026-11-08)
• PostgreSQL 17 — SET and SET LOCAL scope, EXPLAIN and EXPLAIN ANALYZE, planner cost constants (reviewed 2026-11-08)