The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
If a RAG query asks pgvector for 10 nearest neighbors and gets 0, an approximate HNSW scan followed by a selective WHERE filter is one possible explanation—but the title alone cannot establish the cause. pgvector applies filtering after an approximate index scan, so qualifying rows can be missed or fall short of the requested LIMIT. First inspect the SQL and execution plan, then check whether enough rows satisfy the filter and choose a remedy that fits the filter pattern.
Why can a filtered HNSW query return fewer rows than LIMIT?
With an approximate index, pgvector searches a limited portion of the vector index and applies the filter afterward. The project documentation states: “With approximate indexes, filtering is applied after the index is scanned.” If many scanned candidates fail the filter, the result can contain fewer rows than the query’s LIMIT requests.
The pgvector README illustrates the effect: when a filter matches 10% of rows, the documented default hnsw.ef_search value of 40 produces four matching rows on average. That is an illustrative average, not a promise for an individual query—and it does not mean a particular zero-row result has been diagnosed. pgvector: Filtering
A zero-row result also has simpler possible explanations: the filter may match no records, the query may use a different predicate than intended, or another part of the plan may constrain the results. Confirm the facts in your database before changing HNSW settings.
#1 Best Overall
How to diagnose the zero-row result
1. Confirm that qualifying rows exist
Run a count using the same filter predicates as the RAG query, without the vector-distance ordering or limit. If the count is zero, the issue is not that HNSW failed to fill the limit: no records satisfy those conditions. Check values, types, null handling, tenant constraints, and any joins or subqueries that contribute to the filter.
2. Inspect the actual SQL and execution plan
Run EXPLAIN (ANALYZE, BUFFERS) on the query and verify its filter, distance operator and ordering, chosen index, and rows removed or retained at each relevant plan node. The plan shows whether PostgreSQL used the intended HNSW index and where the filtering occurs. Plan choice can vary with query shape and selectivity, so do not infer the execution path from the SQL text alone. The pgvector test suite includes plan checks for filtering and joins. pgvector filtering plan checks
Rank #2
3. Check versions and effective settings
Record the PostgreSQL and pgvector versions and the settings active for the session or transaction that runs the RAG query. Iterative scans require pgvector 0.8.0 or later. Documented defaults such as hnsw.ef_search and scan limits are pgvector defaults, not universal PostgreSQL guarantees; deployments can differ by release or configuration.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Which fix fits your filter pattern?
| Situation | Approach | Trade-off |
|---|---|---|
| Selective filter; exact search is practical | Index the filter column and evaluate exact nearest-neighbor search over qualifying rows. | Can avoid approximate-index under-return for this pattern, but exact distance work may be more expensive as the qualifying set grows. |
| Approximate search with a need to find more qualifying rows | Use an iterative HNSW scan, available from pgvector 0.8.0. | Does more index work and is bounded by scan and memory settings; it cannot return rows that do not satisfy the query. |
| A small, fixed number of filter values | Consider a partial HNSW index for each relevant value. | Can target common fixed predicates, but separate indexes are less suitable when the number of values grows. |
| Many filter values or tenant-specific search | Consider partitioning; for tenant isolation, pgvector documentation suggests list partitioning or separate tables. | Requires a structural data-layout choice rather than just a query-setting change. |
These strategies solve different problems. A regular index on the filter column is a useful starting point for filtered queries and may make exact search practical when the filter is selective. Partial indexes suit a limited set of known values; partitioning is a better fit to consider when values are numerous or data needs tenant boundaries. pgvector: Filtering
How to enable iterative HNSW scans
For pgvector 0.8.0 and later, iterative scans can continue scanning the index to find more rows that survive filtering, until enough are found or a configured bound is reached. Set the mode for the query’s transaction or session before executing the vector search:
BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SELECT ...
FROM items
WHERE ...
ORDER BY embedding <=> $1
LIMIT 10;
COMMIT;Replace the abbreviated query with the application’s actual table, predicates, distance operator, and parameter. Use the operator appropriate to the distance metric and index. SET LOCAL limits the setting to the current transaction; without an explicit transaction, use a session-level setting or configure the application so the setting and query run in the same transaction.
Strict versus relaxed ordering
strict_order preserves exact distance ordering in the returned results. relaxed_order can improve recall while allowing slight out-of-order results. If using relaxed ordering but the final output must be strictly ordered, the pgvector documentation shows using a materialized CTE to sort again; on PostgreSQL 17 or later, its documented outer ordering uses distance + 0.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →WITH nearest AS MATERIALIZED (
SELECT id, embedding <=> $1 AS distance
FROM items
WHERE ...
ORDER BY embedding <=> $1
LIMIT 10
)
SELECT id, distance
FROM nearest
ORDER BY distance + 0;Use this pattern only after adapting it to the actual query and confirming the desired ordering semantics. pgvector: Iterative index scans
Understand scan and memory bounds
The current pgvector documentation gives hnsw.max_scan_tuples a default of 20,000 and describes the limit as approximate; it does not affect the initial scan. The current HNSW source gives hnsw.scan_mem_multiplier a default of 1. These are version- and configuration-sensitive defaults. Raising the tuple limit may allow more search, while increasing the memory multiplier may help when raising the tuple limit alone does not improve recall. Both changes can increase resource use, so validate them against the deployed release and workload rather than treating them as guaranteed fixes. pgvector iterative scan settings pgvector HNSW source
What if the filter comes from a subquery?
A pgvector issue opened on February 13, 2025, reports a concern that iterative scans may depend on the planner applying a condition as an index-scan filter, and that a subquery could not be applied there. This is a report about a particular planner concern, not a rule for every query or PostgreSQL plan. If your filter is produced by a subquery, inspect the actual plan and validate the behavior on your deployed PostgreSQL and pgvector versions. pgvector issue 776
What the zero-row symptom does—and does not—tell you
Filtered approximate HNSW under-return is a documented mechanism, but a request for 10 rows returning 0 does not prove that mechanism caused the incident. The exact cause depends on the query, schema, versions, settings, filter selectivity, plan, and number of qualifying records. Establish those facts first; then use iterative scanning, exact search, a partial index, or partitioning according to the observed filter pattern and the acceptable cost.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
