skip to content

With a pgvector HNSW index, a ticket search filtered by account_id returns 3 rows for LIMIT 10; why, and what does pgvector 0.8 add to fix it?

level: seniorimportance: should knowfreq 46%

answer

  1. filter runs after the index
  2. 40 candidates times selectivity
  3. iterative_scan strict or relaxed
  4. max_scan_tuples bounds the extra work
  5. B-tree, partial index, partition

basics

~20 s

pgvector applies a WHERE filter after the HNSW scan, so only the account's share of the 40 hnsw.ef_search candidates survives. Version 0.8 adds hnsw.iterative_scan, which keeps scanning until enough rows match or hnsw.max_scan_tuples is reached.

solid answer

~40 s

A pgvector approximate index knows nothing about `account_id`: the HNSW scan produces up to `hnsw.ef_search` (40 by default) nearest candidates and PostgreSQL filters them afterwards. If the account owns about 7% of tickets, roughly 3 of 40 survive, so `LIMIT 10` comes back short. Since pgvector 0.8.0, `SET LOCAL hnsw.iterative_scan = strict_order` (or `relaxed_order`) makes the scan resume and fetch more candidates until the limit is met, stopping at `hnsw.max_scan_tuples` (20,000 by default) or a memory cap of `hnsw.scan_mem_multiplier` times `work_mem`; it is off by default, and a capped scan can still return fewer rows. Alternatives: a B-tree on `account_id`, letting the planner run an exact search over a small account; a partial HNSW index for a few hot values; or LIST partitioning by account so each partition has its own index.

code

sql · 11 lines
sql
CREATE INDEX support_tickets_account_idx ON support_tickets (account_id);

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SET LOCAL hnsw.max_scan_tuples = 40000;
SELECT id, subject
FROM support_tickets
WHERE account_id = 7731
ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)
LIMIT 10;
COMMIT;

go deeper

for a junior

Recall that pgvector filters rows after the approximate index scan, so a WHERE clause can shrink results below LIMIT.

for a middle

Explain the arithmetic, hnsw.ef_search candidates times the filter's selectivity, and what hnsw.iterative_scan changes.

for a senior

Show you would choose between a B-tree path, iterative scans, partial indexes and partitioning by account size, and verify each against exact results.

for a principal

Weigh one shared index against per-tenant partitions: recall interference, the cost of many indexes, and how tenant growth shifts the answer.

## Why the filter starves the result The scenario: `support_tickets` holds every customer's tickets, an HNSW index covers `embedding`, and the support app asks for the ten tickets most similar to a new one, **within one customer account**: ```sql SELECT id, subject FROM support_tickets WHERE account_id = 7731 ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42) LIMIT 10; ``` An HNSW or IVFFlat index knows nothing about `account_id`. The index scan returns candidates in approximate distance order, and PostgreSQL applies the `WHERE` clause to each one **after** the index produced it. With iterative scans off, the scan produces at most `hnsw.ef_search` candidates - 40 by default. If account 7731 owns about 7% of all tickets, roughly 3 of those 40 survive, and `LIMIT 10` returns three rows. pgvector's own example: a condition matching 10% of rows leaves about 4 rows on average. ## Iterative index scans (pgvector 0.8.0) Since 0.8.0 the scan can **resume**: when filtering leaves too few rows, it fetches more candidates from the index until the `LIMIT` is satisfied or a cap is reached. It is off by default. | Setting | Values (default first) | Effect | |---|---|---| | `hnsw.iterative_scan` | `off`, `relaxed_order`, `strict_order` | lets an HNSW scan resume | | `hnsw.max_scan_tuples` | 20000 | approximate cap on tuples visited; does not limit the initial scan | | `hnsw.scan_mem_multiplier` | 1, as a multiple of `work_mem` | memory cap for the extra candidates | | `ivfflat.iterative_scan` | `off`, `relaxed_order` | lets an IVFFlat scan probe more lists | | `ivfflat.max_probes` | 32768, the maximum number of lists | cap on lists probed; if lower than `ivfflat.probes`, `ivfflat.probes` is used | - **`strict_order`** guarantees the returned rows are in exact distance order. - **`relaxed_order`** allows results slightly out of order by distance in exchange for better recall; it is the only resuming mode IVFFlat offers. - When a cap is hit first, the query still returns fewer than `LIMIT` rows: iterative scans make a short result rarer, not impossible. If raising `hnsw.max_scan_tuples` does not improve recall, pgvector suggests raising `hnsw.scan_mem_multiplier`. ## The other tools Iterative scans are one of four approaches pgvector documents for filtered queries: 1. **A B-tree on the filter column** - `CREATE INDEX ON support_tickets (account_id)`. For a small account the planner can fetch its rows by B-tree and sort them by exact distance: exact results, cheap. pgvector 0.8.0 also improved cost estimation so the planner chooses better between paths when filtering. This degrades as the account grows. 2. **Iterative HNSW scans** - best when the filter matches a moderate share of rows and the account is too large for an exact sort. 3. **A partial HNSW index** - `USING hnsw (embedding vector_cosine_ops) WHERE (account_id = 7731)` - when queries filter by only a few distinct values, such as a handful of very large accounts. 4. **LIST partitioning by `account_id`** - each partition gets its own HNSW index and the planner prunes the rest. pgvector recommends partitioning or separate tables for **tenant isolation**, because tenants sharing one approximate index affect each other's recall and speed. ## Choosing in the support-ticket case - Many small accounts and a few huge ones: keep a B-tree on `account_id` so small accounts get exact results, and set `hnsw.iterative_scan = strict_order` with `SET LOCAL` so large accounts still fill their `LIMIT`. - Strict per-customer isolation and predictable recall: partition by account, accepting many more indexes to build and maintain. - Measure rather than guess: compare each path's results with an exact search for sample accounts of different sizes, and read the plan to see which path the planner picked. ## Confirming which path ran `EXPLAIN (ANALYZE, BUFFERS)` tells the paths apart: - **HNSW path**: an `Index Scan` on the vector index with an `Order By: (embedding <=> ...)` line, a `Filter: (account_id = 7731)` line, and a `Rows Removed by Filter` count showing how many candidates the filter threw away. - **B-tree path**: a scan of `support_tickets_account_idx` feeding a `Sort` on the distance under the `Limit` - exact results, with cost growing with the account's row count. - A large `Rows Removed by Filter` next to a short result is the signature of the starved post-filter described above. What a tenant filter means for authorization, and filtered nearest-neighbour search in general, are vector-retrieval theory; partitioning mechanics such as partitioned indexes are PostgreSQL DDL topics of their own.

  • When does a plain B-tree on account_id beat iterative HNSW scans in pgvector?
    When the account has few rows. The planner can fetch them through the B-tree and sort by exact distance, giving perfect recall cheaply; pgvector 0.8.0 improved cost estimation for this choice. As an account grows, the exact sort over its rows gets expensive and the HNSW path with iterative scans wins.
  • What do you give up with hnsw.iterative_scan = relaxed_order instead of strict_order?
    Rows may come back slightly out of distance order, in exchange for better recall. If the caller needs strict ordering, use `strict_order`, or re-sort the fetched rows by distance in an outer query; pgvector's documentation shows a materialized CTE pattern for that.
  • Why might you partition support_tickets by account_id instead of tuning iterative scans?
    One shared approximate index lets each tenant's vectors affect the others' recall and speed. LIST partitioning by `account_id`, or separate tables, gives each tenant its own HNSW index and lets the planner prune the rest, at the cost of many more indexes to build and maintain.

It is like asking a librarian for the 40 books shelved nearest a topic and then discarding every one not by your author: with a rare author you leave with three. Iterative scans let you go back for the next armful until you have ten, or until the librarian's patience, max_scan_tuples, runs out.

  • pgvector's HNSW index evaluates the WHERE clause while it walks the graph
  • Iterative index scans are on by default since pgvector 0.8.0
  • Raising LIMIT to 100 makes the filtered HNSW query return more matches
  • ivfflat.iterative_scan supports strict_order just like the HNSW setting
  • With iterative scans on, a filtered query always fills its LIMIT