A pgvector similarity query on 20 million rows is too slow, and sequential scans show up in the plan. What is the most direct fix?
Why C is the answer
Add an approximate index such as HNSW on the embedding column
Without a vector index, Postgres compares every row. An approximate index trades a little recall for a very large speedup.
In detail
- Sequential scans compare every row.
- An approximate index such as HNSW avoids that.
- Approximate search trades a little recall for speed.
Use case
An illustrative scenario, not a real incident.
A similarity query over 20 million rows runs a sequential scan. An HNSW index on the embedding column avoids comparing every row.
See it in different ways
flowchart LR A["Text"] --> B["Embedding model"] B --> C["Vector stored with the item"] D["Query embedding"] --> E["Similarity search"] C --> E E --> F["Closest items"]
stateDiagram-v2 [*] --> SequentialScan: no vector index SequentialScan --> AddIndex: slow on large tables AddIndex --> ApproximateSearch: HNSW ApproximateSearch --> ReviewResources: memory is short ReviewResources --> ApproximateSearch
stateDiagram-v2 state "Sequential scan" as s0 state "HNSW index added" as s1 state "Fast approximate search" as s2 [*] --> s0 s0 --> s1: create index s1 --> s2 s2 --> [*]
Key point: Approximate indexes avoid full scans.
Why the other options are wrong
- A. Move the table to Blob Storage and scan it from a Function
- Scanning from a Function is slower still.
- B. Increase the client connection timeout and the pool size
- A longer timeout just waits for the same slow query.
- D. Convert the embeddings to text columns and use full-text search
- Full-text search does not compare vectors.