RAG: search that answers · Chapter 16 of 37
Indexes that guess
By the end of this chapter you can build HNSW and IVFFlat indexes on a vector column, tune how hard they search, and measure with recall how often their fast answer is the right one. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Vectors in Postgres
- 5 tasks
- In the all labs plan
The first 2 chapters of RAG: search that answers are free. This one comes with the all labs plan.
Why you need this
Kabir wants two years of resolved support tickets searchable next to the help centre: about 100,000 chunks. Chapter 15's search reads every row and sorts them all. On 866 chunks that is nothing; on 100,000 it is about 20 ms of several CPU cores per search, and the assistant runs several searches per question while support agents search as they type.
A vector index answers in under a millisecond by not reading every row. The catch: it guesses. Unlike a B-tree on an id, a vector index can miss the true nearest chunk, and nothing in the result tells you. You need the knobs that trade speed for accuracy, and a number for the accuracy.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
exactSearch: the true top k, with the index switched off
Return the ids of the k rows of table nearest to vector by cosine distance (<=>), best first, exactly. Inside a transaction (BEGIN), run SET LOCAL enable_indexscan = off, then the query with the vector (pgvector.toSql) and k as parameters, then COMMIT, and return the ids.
The Check gives the table a weak HNSW index: if your query uses it, you get its guess instead of the true top k. Afterwards the connection's enable_indexscan must still be on (a plain SET would switch the index off for every later query on it), and no transaction may be left open.
- Task 2
createHnsw: build an HNSW index
Drop the index named table + '_hnsw' if it exists, then build it: CREATE INDEX on table USING hnsw (embedding vector_cosine_ops) WITH (m = m, ef_construction = efConstruction). Return { ms, bytes }: ms is how long the CREATE INDEX took (performance.now() before and after), bytes is pg_relation_size of the index, as a number.
createHnsw(db, table, 8, 32) leaves an index whose options are m=8 and ef_construction=32; a second call with 4 and 10 replaces it.
- Task 3
annSearch: search with the index, ef_search for one query
Return the ids of the k nearest rows, best first, the way the HNSW index finds them with hnsw.ef_search set for this one query: BEGIN, SELECT set_config('hnsw.ef_search', value, true), the same query as exactSearch, COMMIT. set_config takes the value as text and, with true, lasts only for the transaction (SET LOCAL cannot take a parameter).
The index returns at most ef_search rows, so use the larger of efSearch and k: annSearch(db, table, v, 60, 20) returns 60 ids, not 20. Do not switch index scans off. Afterwards the connection's hnsw.ef_search is still 40, the default.
- Task 4 · interview question
Interview: recallAt, recall against the exact scan
For each query vector, take the exact top k (exactSearch) and the index's top k (annSearch with efSearch). Its recall is how many of the exact ids the index returned, divided by k. Return the average over all queries, and 0 for an empty list.
The Check compares your number with its own on a weak index: recall at efSearch 10 is clearly lower than at 200.
Arjun will ask: how do you know the index is good enough, and what does a bigger ef_search cost you?
- Task 5
createIvfflat: an IVFFlat index with the right number of lists
Count the rows of table. lists is rows / 1000 rounded, at least 1, up to a million rows, and the square root of rows (rounded) above that. Drop the index named table + '_ivf' if it exists, build it USING ivfflat (embedding vector_cosine_ops) WITH (lists = lists), and return lists. 3,000 rows give 3 lists; 400 rows give 1.
IVFFlat learns its lists from the rows already in the table, so on an empty table throw an Error whose message says to load the data first, and build nothing.
What you will learn
- The exact scan: what Postgres does without an index, in EXPLAIN ANALYZE.
- HNSW: a graph of neighbours, with m, ef_construction and ef_search.
- IVFFlat: vectors sorted into lists, with lists and probes.
- Recall: the share of the true top k an index finds, measured against the exact scan.
More in Vectors in Postgres
Build "Indexes that guess" yourself
Paisa Setu, a payments company, needs its help centre to answer merchants correctly. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of RAG: search that answers are free. This one comes with the all labs plan.