pgvector 0.8 continues searching after filters discard candidates

A query that asks for five nearest neighbours can return fewer than five even when its table contains enough matching records. With a pgvector approximate index, the database applies ordinary filters after retrieving vector candidates. Some candidates disappear at that stage. pgvector 0.8.0, released on October 30, adds iterative index scans that can search further when the initial candidates do not supply enough results.

The distinction is between nearest-neighbour retrieval over an index and nearest-neighbour retrieval within a filtered collection. A category condition in SQL expresses the latter requirement. An approximate index that first searches the broader collection may spend its initial work on records outside that category. Continuing the scan gives the executor another opportunity to obtain qualifying rows.

I like keeping vector retrieval in PostgreSQL when the records already belong to a relational application. That makes filters a central part of the design, not an optional refinement after search. This release addresses a concrete mismatch between a SQL query's requested result count and the candidate set produced by its approximate access method.

Filtering changes how much search is enough

The 0.8.0 filtering documentation gives an illustrative calculation. With HNSW's default ef_search of 40 and a condition matching ten percent of rows, about four candidates match on average. That is an explanation of candidate attrition, not a guarantee for every query. The condition's relationship to vector proximity can affect which retrieved records qualify.

Increasing hnsw.ef_search enlarges the candidate list and can supply more qualifying rows. It also chooses a larger initial search effort. The new iterative option instead allows the scan to continue when the executor still needs results. Both affect the work spent searching, but they control different points in the process.

The following query assumes an items table with a three-dimensional embedding column and a category_id column. Its category condition is part of the requested result, while the ordering uses L2 distance.

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

The transaction-local setting enables the new behavior for this scope. An accompanying index would use HNSW with vector_l2_ops on embedding. The SQL still asks for five records within the category. Iteration changes how the index scan can respond when earlier candidates fail that condition.

This does not establish that the answer is the exact nearest-neighbour set across every matching row. HNSW remains an approximate access method. Returning enough qualifying candidates and achieving perfect recall are separate properties. Iterative scanning addresses the former problem while offering additional search effort that can also affect recall.

Ordering and recall have separate meanings

The iterative scan documentation provides strict and relaxed ordering modes. Strict ordering guarantees that returned results are in exact distance order. That describes the order of the results the scan returns. It does not convert approximate retrieval into an exhaustive evaluation of the table.

Relaxed ordering permits results to arrive slightly out of distance order and provides better recall according to the documentation. HNSW exposes this as hnsw.iterative_scan = relaxed_order. IVFFlat's iterative option also uses relaxed ordering through ivfflat.iterative_scan. The supported modes should be read for each access method rather than assumed to be identical.

For an application that needs sorted output, the README shows a materialized common table expression followed by an outer ordering. That separates retrieval from presentation of the retained rows. The inner query determines which records enter the materialized result, and the outer query sorts those records by their computed distance.

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

This query shape is useful with relaxed scanning because it restores order among the retained candidates. It cannot add records absent from the materialized result. That distinction resembles reranking after a reduced-precision search, but here the input issue is the scan's ordering mode. The outer sort has a defined set of rows on which to operate.

An application's result contract should therefore distinguish candidate membership from ordering. A correctly sorted set can still omit a closer row that approximate retrieval did not find. Conversely, a relaxed scan can find useful neighbours while requiring a final sort for the output order. The release makes that choice explicit through the mode setting and the query structure.

Additional work has bounded settings

The scan-option documentation explains why iteration needs limits. Searching a large portion of an approximate index is expensive. HNSW uses hnsw.max_scan_tuples, documented with a default of 20,000, to limit visited tuples approximately. The documentation also states that this setting does not affect the initial scan.

Consequently, that value is not an exact count of all work performed by the query. It constrains the additional scanning behavior with the qualification given by the implementation's documentation. Describing it as an absolute per-query tuple ceiling would erase both the approximation and the initial-scan exception.

HNSW also has hnsw.scan_mem_multiplier, a multiplier of work_mem, with a documented default of one. The README recommends considering an increase if raising the tuple limit does not improve recall. A larger allowed search does not establish that the scan has enough memory to make useful progress. Those controls describe separate resource requirements.

IVFFlat instead exposes ivfflat.max_probes for the maximum probe count during iteration. If it is set below the initial ivfflat.probes value, the latter takes precedence. This preserves a distinction between the configured initial search and the later extension of that search. The upper option cannot silently reduce the initial probing already requested.

The scan can stop when it has enough results or reaches its configured limit. An underfilled result therefore remains possible when limits prevent further work. Iteration should not be presented as an unconditional promise that every SQL limit is filled. The feature gives the scan a continuation path with explicit stopping conditions.

I consider those limits part of the feature's usefulness. The database can pursue additional qualifying neighbours while an application retains a way to bound the extra search effort. That is more informative than treating a short result as a mysterious property of vector data or increasing every query's initial candidate count without distinction.

Relational selectivity can favor a different index

The release also improves cost estimation for index selection when filtering. Its filtering guidance starts with an ordinary index on the filter column, which can support fast exact nearest-neighbour search when relatively few rows match. In that arrangement, restricting the collection first can leave a manageable set over which to evaluate distances exactly.

A partial approximate index is another option for a small number of distinct filter values. The documentation shows an HNSW index restricted to one category. Partitioning is suggested when filtering involves many values. Those choices alter the indexed collection itself, while iteration extends search through candidates in an existing approximate index.

A category-specific index changes which records can become candidates before the scan begins. That differs from increasing the work on a global graph and then discarding candidates from other categories. Both can support the same visible WHERE condition, but their definitions place the restriction at different stages. Choosing between them depends on the actual collection that the application needs to search.

For an exact search over a selective category, the relevant correctness expectation also differs. The README describes exact nearest-neighbour search as providing perfect recall. An ordinary category index can narrow the rows without introducing the approximation associated with an HNSW or IVFFlat index. The planner therefore has alternatives whose costs and result properties differ, which explains why the cost-estimation change belongs alongside iterative scanning in this release. A decision about scan continuation should preserve those alternatives when describing what the SQL can do.

Distance thresholds have their own documented query structure. For a query that filters by distance, the README recommends putting the distance condition outside a materialized CTE because of current executor behavior. Other filters belong inside that CTE. This is a concrete placement rule for that pattern, and differs from claiming that every predicate can move freely across the materialization boundary.

The release also improves HNSW scans, inserts and on-disk builds, adds array casts to sparsevec and drops PostgreSQL 12 support. For filtered nearest-neighbour queries, its main new behavior is the ability to continue after candidate rejection. Index definition, selectivity and scan limits still determine how the query obtains its qualifying rows and when further search ends.