Recommended Free Tools
A pgvector query can return fewer rows than its LIMIT—even zero—when an approximate HNSW scan finds candidates and a WHERE filter removes them afterward. That is a plausible explanation for “my RAG asked for 10 rows and got 0,” not a diagnosis of this particular incident: the SQL, execution plan, pgvector version, and number of matching records are unknown.
Why can a filtered HNSW query return fewer rows than LIMIT?
With an approximate index, pgvector applies filtering after scanning the index. The scan may produce nearby candidates, then the filter discards candidates that do not qualify. If too few candidates survive, the result contains fewer rows than the requested limit; it can contain none.
As an Amazon Associate I earn from qualifying purchases.
As an illustration, pgvector’s current README says that when a filter matches 10% of rows, the default hnsw.ef_search value of 40 yields four matching rows on average. That is an average from the documentation’s example, not a guarantee for an individual query or an explanation of every zero-row result. The default is version-sensitive. pgvector documentation
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsHow to diagnose the zero-row result
1. Verify that any rows satisfy the filter
First check the filter independently of vector search. Count records meeting the exact conditions used by the RAG query. If fewer than 10 records qualify, no index setting can make the query return 10 qualifying rows. If the count is zero, investigate the filter values, joins, tenant scope, and underlying data before changing HNSW settings.
#1 Best Overall
2. Inspect the executed query and plan
Run the actual query with EXPLAIN (ANALYZE, BUFFERS). Check that the query uses the intended distance operator and ordering, which index PostgreSQL chose, where the filter is applied, and how many rows remain after filtering. An index existing in the schema does not prove the planner used it. Plan choices can vary with query shape and selectivity; pgvector’s test suite includes plan checks for filtering and joins. pgvector test suite and documentation
3. Check versions and session settings
Record the PostgreSQL and pgvector versions and inspect relevant HNSW settings in the same session that runs the query. Iterative scans require pgvector 0.8.0 or later. Defaults and planner behavior are not universal across releases or configurations.
4. Treat subquery filters as a plan-specific case
A 2025 discussion in pgvector issue #776 raised a concern that iterative scans may depend on the planner applying a condition as an index-scan filter, and that a filter through a subquery might not be applied there. This is a reported concern, not a general rule for all plans. If your filter comes through a subquery, inspect the actual plan and validate behavior on your deployed PostgreSQL and pgvector versions. pgvector issue #776
How to get enough qualifying neighbors
Try iterative HNSW scans
Starting with pgvector 0.8.0, iterative scans can continue searching when filtering leaves too few results, subject to configured limits and available memory. For a session-level test, run:
SET hnsw.iterative_scan = strict_order;Then rerun the query and compare its plan, returned rows, and execution cost. Strict ordering preserves exact distance order among returned results. Iteration can do more work, but it cannot return 10 rows if fewer than 10 records satisfy the query’s conditions. pgvector iterative index scans
Consider relaxed ordering when recall matters more than immediate order
Relaxed ordering allows slight out-of-order results and can improve recall. If the final output must be strictly ordered by distance, pgvector documents using a materialized CTE to restore ordering after a relaxed scan. On PostgreSQL 17 or later, its documented outer ordering uses distance + 0. Check the current documentation for the exact query pattern for your version before adopting it. pgvector iterative index scans
Review scan limits and memory
The pgvector documentation and HNSW source list a default hnsw.max_scan_tuples of 20,000 and a default hnsw.scan_mem_multiplier of 1. These are pgvector defaults, not PostgreSQL guarantees. The tuple limit is approximate and does not affect the initial scan. If iteration stops before finding enough qualifying rows, raising the tuple limit may allow more search, at the cost of additional work. If that does not improve recall, increasing the scan memory multiplier may help. Tune against real query performance and recall rather than assuming larger limits are free. pgvector HNSW settings
Choose a filtering strategy for your data
| Filter pattern | Approach to consider | Trade-off or qualification |
|---|---|---|
| Selective filter with a useful filter-column index | Try exact nearest-neighbor search over qualifying rows. | Exact search may be practical when few records qualify; confirm with the actual plan and workload. |
| A small number of recurring filter values | Consider a partial HNSW index for each relevant value. | Partial indexes suit a small set of values; they require managing an index for each covered condition. |
| Many distinct filter values | Consider partitioning. | Partitioning can narrow the search to the relevant subset, but adds schema and operational complexity. |
| Tenant-specific retrieval | Consider list partitioning or separate tables for tenant data. | A shared approximate index can let one tenant’s vectors affect another tenant’s recall and speed. |
These options address different data shapes; they are not interchangeable fixes. pgvector recommends a regular index on the filter column as a starting point for filtered queries, and describes partial indexes and partitioning for differing numbers of filter values. pgvector filtering guidance
What the result does—and does not—tell you
A zero-row result is consistent with approximate HNSW candidates being removed by a filter, but it does not establish that as the cause. The decisive checks are whether qualifying rows exist and what the executed plan does. Once those are known, choose between exact search, iterative scanning, and a data-layout change based on selectivity, filter cardinality, required ordering, and acceptable query 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.
