skip to content

In pgvector, why can an HNSW query with LIMIT 100 return only 40 rows, and how do you raise recall for one query?

level: middleimportance: should knowfreq 40%

answer

  1. dynamic candidate list size
  2. default 40, maximum 1000
  3. SET LOCAL inside a transaction
  4. m and ef_construction live in WITH

basics

~20 s

pgvector's HNSW scan returns at most hnsw.ef_search candidates, 40 by default, so LIMIT 100 can yield 40 rows or fewer. Raise it for one query with SET LOCAL hnsw.ef_search = 200 inside a transaction, or turn on iterative scans.

solid answer

~50 s

`hnsw.ef_search` is a pgvector configuration parameter (any user can set it; default 40, range 1-1000) that sizes the dynamic candidate list the HNSW search keeps. With `hnsw.iterative_scan` at its default `off`, that list is everything the index scan can return, so `LIMIT 100` gets at most 40 rows - fewer if some candidates are dead tuples or fail a `WHERE` clause - with no error. Fix it per query: `BEGIN; SET LOCAL hnsw.ef_search = 200; SELECT ...; COMMIT;` keeps the change inside one transaction, so it cannot leak to the next user of a pooled connection. A larger value raises recall and costs latency. Past 1000, or when filters thin the list, turn on `hnsw.iterative_scan`. `m` (default 16) and `ef_construction` (default 64) are different: they are index storage parameters given in `CREATE INDEX ... WITH (...)` and shape the graph itself.

code

sql · 11 lines
sql
CREATE INDEX CONCURRENTLY support_tickets_embedding_hnsw_idx
    ON support_tickets USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

BEGIN;
SET LOCAL hnsw.ef_search = 200;
SELECT id, subject
FROM support_tickets
ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)
LIMIT 100;
COMMIT;

go deeper

for a junior

Recall that hnsw.ef_search is a session setting, default 40, and that a larger value means better recall but slower queries.

for a middle

Explain why the candidate list caps the rows an HNSW scan returns, and why SET LOCAL is the safe way to change it per query.

for a senior

Show you can tell a query-time recall problem from a build-time one, and plan an online rebuild when m or ef_construction must change.

for a principal

Discuss recall targets per workload, such as interactive search versus batch deduplication, and who owns the latency budget each implies.

## Three HNSW knobs, two places to set them | Name | Where it is set | Default | Range | What it changes | |---|---|---|---|---| | `m` | `CREATE INDEX ... WITH (m = ...)` | 16 | 2-100 | maximum connections per node per layer: graph size, recall, build time | | `ef_construction` | `CREATE INDEX ... WITH (ef_construction = ...)` | 64 | 4-1000, and at least `2 * m` | candidate list while building: recall, build time, insert speed | | `hnsw.ef_search` | `SET` or `SET LOCAL`, by any user | 40 | 1-1000 | candidate list while searching: recall, latency, rows returned | `m` and `ef_construction` are **index storage parameters**: they are baked into the graph when it is built, so changing them means building a new index. `hnsw.ef_search` is a **configuration parameter** that pgvector registers, user-settable like `work_mem`, and read on every scan. What these parameters mean for graph search in general is HNSW theory; this answer is about pgvector's surface. ## Why LIMIT 100 can return 40 rows The HNSW search keeps a **dynamic candidate list** of size `hnsw.ef_search` while it walks the bottom layer of the graph. With `hnsw.iterative_scan` at its default, `off`, that list is the whole pool the index scan can return: - `LIMIT 10` with the default 40: the ten best of forty candidates - fine. - `LIMIT 100` with the default 40: at most 40 rows, with no error and no warning. - Fewer still when some candidates are dead tuples not yet vacuumed, or fail a `WHERE` clause that PostgreSQL applies after the index scan. pgvector's troubleshooting notes describe exactly this case: results are limited by the size of the dynamic candidate list. ## Raising it for one query ```sql BEGIN; SET LOCAL hnsw.ef_search = 200; SELECT id, subject FROM support_tickets ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42) LIMIT 100; COMMIT; ``` 1. `SET LOCAL` lasts until the transaction commits or rolls back, then the value reverts. 2. A plain `SET` lasts for the whole session - on a pooled connection it carries over into the next client's queries. 3. Keep `hnsw.ef_search` at least as large as the `LIMIT`; larger values raise recall and cost latency. ## Choosing a value - Start from the default and raise it until measured recall - compared against an exact search with index scans disabled - meets the target. - Values above 1000 are rejected: PostgreSQL reports that the value is outside the valid range for the parameter. - For result sets larger than that, or when filters thin the candidate list, use **iterative scans** (`hnsw.iterative_scan`, pgvector 0.8.0 and later), which resume the search for more candidates instead of enlarging one list. - Different workloads can carry different values: an interactive ticket search might accept the default, while a nightly duplicate-ticket job sets a high value in its own transaction. ## When the build parameters are the problem If recall stays low even with a high `hnsw.ef_search`, the graph itself may be too sparse: - Rebuild with a higher `m` or `ef_construction`; pgvector's advice is to keep the defaults unless you are seeing low recall. - `ef_construction` must be at least `2 * m`: `WITH (m = 48, ef_construction = 64)` fails with `ef_construction must be greater than or equal to 2 * m`. - Higher values make the build slower and the index larger. Build the replacement with `CREATE INDEX CONCURRENTLY` alongside the old index, check its recall, then drop the old one. ## A quick diagnosis checklist 1. Count the rows returned versus the `LIMIT`. Short, with no filter: `hnsw.ef_search` is below the `LIMIT`. 2. Short, with a filter: the filter is discarding candidates; raise `hnsw.ef_search` or enable iterative scans. 3. Full count but poor recall against exact search: raise `hnsw.ef_search` first, then consider rebuilding with larger `m` or `ef_construction`. 4. Recall fine but latency too high: lower `hnsw.ef_search`, or shrink the vectors (half precision) so more of the index stays in memory.

  • What does pgvector do with CREATE INDEX ... USING hnsw ... WITH (m = 48, ef_construction = 64)?
    The build fails with `ef_construction must be greater than or equal to 2 * m`: 64 is below 96. Raise `ef_construction` to at least 96 or lower `m`.
  • Why use SET LOCAL rather than SET for hnsw.ef_search in an application?
    `SET` lasts for the session, so on a pooled connection the raised value leaks into whatever the next client runs. `SET LOCAL` reverts at `COMMIT` or `ROLLBACK`, which scopes the cost of a large candidate list to the one query that needs it.
  • Can you set hnsw.ef_search to 5000 for a LIMIT 2000 query?
    No - the parameter accepts 1 to 1000 and PostgreSQL rejects anything outside that range. For larger result sets enable `hnsw.iterative_scan`, which keeps fetching candidates up to `hnsw.max_scan_tuples`.

  • hnsw.ef_search is set in CREATE INDEX ... WITH, so changing it needs a rebuild
  • LIMIT 100 always returns 100 rows from an HNSW index on a large table
  • SET m = 32 for the session makes the existing HNSW graph denser
  • A plain SET hnsw.ef_search on a pooled connection affects only the current query
  • ivfflat.probes controls how many candidates an HNSW scan keeps