In pgvector, when does a nearest-neighbour query use an HNSW or IVFFlat index, and how do its results differ from a scan's?
answer
- exact until you index
- bare operator, ascending, LIMIT
- approximate trades recall for speed
- compare with index scans off
basics
~20 sWithout an index, pgvector searches exactly, with perfect recall. An HNSW or IVFFlat index is used only for ORDER BY a matching distance operator, ascending, plus LIMIT, and its results are approximate: true neighbours can be missed and fewer rows returned.
solid answer
~50 sBy default pgvector computes **exact** nearest neighbours: the table is scanned, every distance is computed and a top-N sort keeps the closest, so recall is perfect. An HNSW or IVFFlat index switches to **approximate** search, and the planner considers it only when the query has `ORDER BY embedding <op> q` - the bare operator from the index's operator class, ascending - and a `LIMIT`. `ORDER BY 1 - (embedding <=> q) DESC`, a `WHERE embedding <-> q < 0.5` without ORDER BY and LIMIT, or a small table where a scan is cheaper all stay exact. Once the index is used, results change: a true neighbour can be missed, and fewer rows than `LIMIT` can come back because of `hnsw.ef_search`, `ivfflat.probes`, dead tuples or a filter. Measure recall by rerunning sampled queries with `SET LOCAL enable_indexscan = off` and comparing the IDs.
code
sql · 12 lines-- approximate: uses the HNSW cosine index
SELECT id FROM support_tickets
ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)
LIMIT 10;
-- exact: same query with index scans disabled for one transaction
BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM support_tickets
ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)
LIMIT 10;
COMMIT;go deeper
Recall that pgvector is exact until you add an HNSW or IVFFlat index, and that the index makes results approximate.
Explain the query shape the planner needs: bare operator, ascending, matching operator class and LIMIT, and why a derived score loses the index.
Show how you would measure recall with SET LOCAL enable_indexscan = off on sampled queries and decide when exact search is good enough.
Treat recall as a product metric with a budget: who accepts the accuracy traded for latency, and how it is monitored as data changes.
## Exact search is pgvector's default With no vector index, a query such as `ORDER BY embedding <=> q LIMIT 10` is executed as a scan of the table - possibly a parallel one - that computes the distance for every row and keeps the closest ten with a top-N sort. That is **exact nearest-neighbour search**: recall, the share of the true nearest neighbours that the query returns, is perfect by definition. Its cost grows with every row in the table, which is why pgvector offers approximate indexes. ## When the planner can use HNSW or IVFFlat An HNSW or IVFFlat index is an **ordering** index: it hands rows back in approximate distance order. The planner considers it only when all of these hold: 1. The query has an `ORDER BY` on the **bare distance operator** - `embedding <=> q`, not `1 - (embedding <=> q)` and not `cosine_distance(embedding, q)`. 2. The order is **ascending**, the default; `DESC` cannot use it. 3. The operator belongs to the index's **operator class** - `<=>` for `vector_cosine_ops`, `<->` for `vector_l2_ops`, and so on. 4. There is a **`LIMIT`**; pgvector's documentation states that a query needs both `ORDER BY` and `LIMIT` to use the index. 5. The index path is **cheaper** than a scan; on a small table a sequential scan may win. A filter such as `WHERE embedding <-> q < 0.5` on its own is evaluated row by row on a scan; to use the index it has to be combined with `ORDER BY ... LIMIT`. ## What changes once the index is used | | Exact scan | HNSW | IVFFlat | |---|---|---|---| | Recall | perfect | high, tunable | depends on lists and probes | | Rows returned | up to `LIMIT` | at most `hnsw.ef_search` (40) unless iterative scans are on | limited to the rows in the probed lists | | Query-time knob | none | `hnsw.ef_search` | `ivfflat.probes` | | Cost as the table grows | every row, every query | a small part of the graph | the probed lists only | pgvector's documentation is blunt about it: unlike a typical index, you will see different results for queries after adding an approximate index. Some true neighbours may be missing, and the result can hold fewer rows than `LIMIT` because of the candidate-list size (`hnsw.ef_search`), the number of probed lists (`ivfflat.probes`), dead tuples not yet vacuumed, or a `WHERE` filter applied after the index scan. None of this raises an error; the query simply returns a different, sometimes shorter, list. ## Measuring recall pgvector suggests measuring recall inside PostgreSQL: run the same query with index scans disabled for one transaction, so the planner falls back to an exact scan, and compare the IDs with the indexed result. ```sql BEGIN; SET LOCAL enable_indexscan = off; -- exact search for this transaction only SELECT id FROM support_tickets ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42) LIMIT 10; COMMIT; ``` - Do this for a sample of real query vectors, not one. - Count the overlap between the exact and approximate top-k; that ratio is recall at k. - Repeat after large data loads and after changing `hnsw.ef_search`, `ivfflat.probes` or the index build parameters. `enable_indexscan` is a general planner switch; here it is only a convenient way to get the exact answer for comparison, scoped by `SET LOCAL` so it never leaks into other work. ## Keeping exact search fast enough Exact search is often the right answer for small tables or heavily filtered subsets: - Raise `max_parallel_workers_per_gather` so the scan runs in parallel. - The planner does not count vectors stored out of line (TOAST) when it costs a scan, so a serial scan can look cheaper than it is. pgvector's documentation suggests lowering `min_parallel_table_scan_size` and `parallel_setup_cost` for the query, or `ALTER TABLE ... ALTER COLUMN embedding SET STORAGE PLAIN` while rows still fit on a page. - A B-tree on a selective filter column, such as `account_id`, lets PostgreSQL compute exact distances for that subset only. ## Reading the plan `EXPLAIN (ANALYZE, BUFFERS)` shows an `Index Scan using ...` node with an `Order By: (embedding <=> ...)` line when the vector index is used. When it is not, the plan shows a `Limit` over a `Sort` over a `Seq Scan` (or a `Gather` of parallel scans). If you expected the index and see the scan, check the five conditions above before touching any setting.
- Why might a pgvector exact search refuse to go parallel on a large table?The planner does not count out-of-line (TOAST) vector storage when costing the scan, so a serial scan looks cheaper than it is. pgvector's documentation suggests lowering `min_parallel_table_scan_size` and `parallel_setup_cost` with `SET LOCAL` for the query, or storing vectors inline with `SET STORAGE PLAIN` when rows still fit on a page.
- Does a pgvector query with only WHERE embedding <-> q < 0.5 use the HNSW index?No. The index serves an ordering, so it needs `ORDER BY embedding <-> q` and a `LIMIT`; a bare distance predicate is evaluated row by row on a scan. To find neighbours within a threshold, order and limit first, then apply the distance filter to that result.
- How do you confirm in a test that a pgvector query can use its index at all?Run it inside a transaction with `SET LOCAL enable_seqscan = off`; if the plan still shows a sequential scan, the query shape or operator class is wrong rather than the cost estimate.
- An HNSW index returns exactly the rows a sequential scan would, only faster
- pgvector always searches approximately, even when the column has no index
- WHERE embedding <-> q < 0.3 on its own is enough to use the HNSW index
- Recall cannot be measured inside PostgreSQL, only with an external benchmark
- Ordering by a similarity score computed from <=> still uses the index