# Why pgvector similarity search is slow, and how to fix it without leaving Postgres

> Source: https://netray.co/troubleshoot/pgvector-slow-queries
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: On-Prem AI Troubleshooting

## Short answer

pgvector queries are almost always slow because of an index and operator class mismatch (an index built for one distance function while queries use a different operator), a missing index entirely so Postgres falls back to a sequential scan, or search-time parameters (ef_search, probes) set too low. Confirm EXPLAIN ANALYZE shows an index scan, match the operator class to your distance function, and tune maintenance_work_mem before building large HNSW indexes.

## Error Title

pgvector similarity queries are slow

## Error Signature

pgvector query taking seconds instead of milliseconds

## Alternate Signatures

- SELECT ... ORDER BY embedding <=> query_vector LIMIT 10 is slow
- pgvector index not being used
- sequential scan on vector column instead of index scan

## Category

rag-retrieval

## Stack

- vector-db

## Affects

pgvector on any Postgres version, most noticeable once a table passes a few hundred thousand rows without a properly matched index.

## Quick Fix

### Fastest path to fast pgvector queries

**Steps:**

- Run EXPLAIN ANALYZE on the slow query and confirm it shows an index scan on the vector column, not a sequential scan.
- Confirm the operator class used to build the index (vector_l2_ops, vector_cosine_ops, or vector_ip_ops) matches the distance operator used in the query (<->, <=>, or <#>); a mismatch silently disables the index.
- For HNSW indexes, raise hnsw.ef_search for the session to trade a small amount of latency for recall.
- For IVFFlat indexes, raise ivfflat.probes since the default of 1 probe searches only a single list and misses most of the table.
- Run VACUUM ANALYZE on the table if it has had heavy inserts/updates/deletes, since bloat degrades both scan types.
- If building an HNSW index fails or is extremely slow, raise maintenance_work_mem for the session before running CREATE INDEX.

## Symptoms

- A similarity query that should return in milliseconds takes hundreds of milliseconds to several seconds
- EXPLAIN ANALYZE shows Seq Scan instead of Index Scan on the embedding column
- Query latency degrades sharply as the table grows past a few hundred thousand rows
- Recall looks fine on a small test set but real queries frequently miss obviously relevant rows
- CREATE INDEX for HNSW runs for a very long time or fails with a memory-related error

## Causes

### No vector index exists, or it was never built successfully

**Likelihood:** most-common

**Explanation:** Without an HNSW or IVFFlat index, pgvector falls back to a brute-force sequential scan that computes distance against every row in the table. This works fine for a few thousand rows but scales linearly and becomes unacceptably slow once the table reaches tens or hundreds of thousands of rows.

**Fix:** Create an appropriate index: an HNSW index with vector_cosine_ops is the best default choice, or an IVFFlat index with lists set to roughly the square root of the row count.

**Commands:**

- CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
- EXPLAIN ANALYZE SELECT id FROM items ORDER BY embedding <=> '[...]' LIMIT 10;

### Index built with the wrong operator class for the distance function used at query time

**Likelihood:** common

**Explanation:** pgvector indexes are built for a specific operator class: vector_l2_ops for Euclidean distance, vector_cosine_ops for cosine distance, and vector_ip_ops for inner product. If the index was built with one operator class but the query uses a different operator, Postgres cannot use that index for the query and silently falls back to a sequential scan with no error message.

**Fix:** Check the index definition and confirm the operator class matches the operator used in ORDER BY; if it does not, drop and rebuild the index with the correct operator class rather than changing the query, since the operator class should match the distance metric your embeddings were normalized for.

**Commands:**

- SELECT indexdef FROM pg_indexes WHERE tablename = 'items';

### ef_search or probes set too low for the workload

**Likelihood:** occasional

**Explanation:** HNSW's ef_search and IVFFlat's probes control the search-time speed/recall tradeoff. Low default values prioritize speed over recall, which can look like slow queries when what is actually happening is fast-but-wrong results that require the application layer to retry or over-fetch to compensate.

**Fix:** Increase ef_search (HNSW) or probes (IVFFlat) incrementally and measure both latency and recall against a labeled test set to find the setting that meets your recall target without unnecessary latency.

**Commands:**

- SET hnsw.ef_search = 100;
- SET ivfflat.probes = 10;

### Table bloat from heavy insert/update/delete activity

**Likelihood:** occasional

**Explanation:** Postgres does not immediately reclaim space from updated or deleted rows; dead tuples accumulate and both sequential scans and, to a lesser extent, index scans have to skip over them, adding overhead that grows with churn and shows up as gradually worsening query times.

**Fix:** Run VACUUM ANALYZE regularly on high-churn vector tables, and consider more aggressive autovacuum settings for that table specifically if updates are frequent.

**Commands:**

- VACUUM ANALYZE items;

### Not enough maintenance_work_mem to build the HNSW index efficiently

**Likelihood:** rare

**Explanation:** Building an HNSW index is memory-intensive; if maintenance_work_mem is too low, Postgres has to spill graph-building work to disk, which can make index creation extremely slow or, in constrained environments, fail outright partway through.

**Fix:** Raise maintenance_work_mem for the session running CREATE INDEX, then reset it afterward if it was raised globally.

**Commands:**

- SET maintenance_work_mem = '2GB';
- CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

## Diagnostics

### Confirm whether the index is actually being used

**Command:** EXPLAIN ANALYZE SELECT id FROM items ORDER BY embedding <=> '[0.1, 0.2]' LIMIT 10;

**Interpretation:** If the plan shows Seq Scan, the index either does not exist, uses the wrong operator class for this query's operator, or the planner has chosen not to use it; if it shows Index Scan using an hnsw or ivfflat index, the index is working and the bottleneck is elsewhere.

### List existing indexes and their operator classes

**Command:** SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'items';

**Interpretation:** Compare the operator class in the index definition against the operator your application actually uses in ORDER BY; any mismatch means the index is silently unused for that query.

### Check table bloat and dead tuple count

**Command:** SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'items';

**Interpretation:** A high ratio of dead to live tuples indicates bloat; run VACUUM ANALYZE and re-test query latency to see how much of the slowdown was bloat versus indexing.

### Measure recall and latency across a range of ef_search/probes values

**Command:** for v in 40 80 120 200; do echo ef_search=$v; done

**Interpretation:** Use this sweep against a labeled query set to plot the recall/latency tradeoff curve for your data, since the right value is workload-specific and not a single universal default.

## Prevention

- Always build an explicit HNSW or IVFFlat index immediately after loading a vector table; never rely on the default sequential scan.
- Standardize on one distance metric across embedding generation, index operator class, and query operator, and document it so it cannot silently drift.
- Schedule regular VACUUM ANALYZE on high-churn vector tables rather than relying solely on autovacuum defaults.
- Set maintenance_work_mem appropriately before any large index build, and treat index rebuilds as a planned maintenance operation for large corpora.
- Track query latency and recall together in monitoring, since optimizing one without measuring the other leads to silently degraded retrieval quality.

## When To Escalate

If query latency is still unacceptable after confirming index usage, operator class match, tuned ef_search/probes, and a clean vacuum, pgvector on a single Postgres instance may simply be undersized for the corpus and query volume. Consider read replicas, partitioning, or migrating high-QPS collections to a dedicated vector database, which is worth validating against measured load rather than more parameter tuning.

## Faq

### Should I use HNSW or IVFFlat for pgvector?

**Answer:** HNSW is the better default for most workloads: it gives better recall at a given latency and does not require retraining lists as data grows, at the cost of higher memory usage and slower index builds. IVFFlat can be a reasonable choice for very large, mostly-static collections where memory is constrained.

### Why does EXPLAIN ANALYZE show a sequential scan even though I created an index?

**Answer:** The most common reason is an operator class mismatch between the index and the query's distance operator. Verify with pg_indexes that the operator class matches exactly what your query uses.

### Is pgvector fast enough for production RAG at scale?

**Answer:** Yes, for most enterprise RAG workloads, up to tens of millions of vectors, pgvector with a properly configured HNSW index and tuned ef_search performs competitively with dedicated vector databases, while keeping data in the same Postgres instance, which simplifies operations for on-prem and air-gapped deployments.

### What is a good starting value for ef_search or probes?

**Answer:** Start with ef_search around 100 for HNSW or probes around 10 for IVFFlat with roughly 100 lists, then measure recall against a labeled query set and adjust. There is no universal correct value; it depends on embedding dimensionality, corpus size, and recall requirements.

## Search terms

pgvector slow query, pgvector index not used, pgvector HNSW vs IVFFlat, pgvector sequential scan, pgvector ef_search, pgvector operator class, vector database Postgres performance, pgvector maintenance_work_mem

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json