In pgvector, why can an HNSW query with LIMIT 100 return only 40 rows, and how do you raise recall for one query?
answer
- dynamic candidate list size
- default 40, maximum 1000
- SET LOCAL inside a transaction
- m and ef_construction live in WITH
basics
~20 spgvector'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 linesCREATE 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
Recall that hnsw.ef_search is a session setting, default 40, and that a larger value means better recall but slower queries.
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.
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.
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