The application says pgvector is slow. The DBA runs EXPLAIN and sees a sequential scan, a distance sort, a tenant filter, and a LIMIT 10 that arrives too late to save the query. The problem is not that Postgres cannot do vector search. The problem is that nobody read the plan like a database engineer.

Short Version

pgvector query tuning starts with the shape of the SQL plan. DBAs need to verify whether the query uses a vector index, whether ORDER BY embedding <=> $query_vector is written in an indexable form, whether filters are applied before or after candidate selection, whether LIMIT is helping, and whether row estimates match reality.

Do not tune vector search from application latency alone. Use EXPLAIN, representative parameters, realistic tenants, and side-by-side exact-search baselines. The plan tells you whether the database is scanning, sorting, using an approximate index, filtering away candidates, or misestimating selectivity.

pgvector planner behavior, iterative scans, and supported index patterns depend on pgvector and PostgreSQL versions — most notably, hnsw.iterative_scan and ivfflat.iterative_scan only exist from pgvector 0.8.0 (October 2024) onward, and earlier versions will error on the SET rather than silently ignore it. Confirm the deployed pgvector version before assuming any specific planner behavior described below is available.

Situation

Once pgvector is in production, slow retrieval tickets land on the same desks as ordinary PostgreSQL performance tickets. The difference is that answer quality, filters, and approximate index behavior are now mixed into the query plan conversation.

The Problem

Vector SQL looks deceptively ordinary:

SELECT id, body
FROM document_chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

A DBA sees a filter, an order, and a limit. But this query has special requirements:

  • The distance operator must match the index operator class.
  • The ordering must be in a form the index can support.
  • Filters can reduce or distort the candidate set.
  • The planner may choose a sequential scan if it believes that is cheaper.
  • Approximate indexes can return fast but not exact results.
  • LIMIT controls top-k retrieval, but only if the plan can exploit it.

The failure mode is easy to miss in dashboards. Latency goes up, answer quality goes down, or the query returns fewer rows than expected. The plan explains which one is happening.

Core Technical Explanation

Read a pgvector plan from the top down and then from the access path up.

The first question: is the database using a vector index or scanning the table? For large tables, a sequential scan plus sort usually means Postgres is computing distance for many rows and sorting them. That can be acceptable for tiny tables or offline validation, but it is not a production retrieval path for large corpora.

The second question: is the distance expression indexable? This follows the same general PostgreSQL rule that applies to any index-backed ORDER BY: the planner can only use an index to satisfy an ordering if the expression in ORDER BY matches the index’s indexed expression and operator class exactly. The query should order directly by the vector distance expression (embedding <=> $2) and use a LIMIT. Wrapping the distance expression in a function call, casting it, or reversing the comparison changes the expression the planner sees and can silently fall back to a sequential scan plus sort — with no error, just a much slower plan.

The third question: where do filters apply? Tenant, lifecycle, entitlement, category, language, and model-version filters are correctness requirements. But approximate nearest-neighbor indexes may produce candidates before all filters are satisfied. If many candidates are filtered out, the result can under-return or require broader search settings.

The fourth question: are estimates believable? If Postgres expects a tenant filter to return half the table when it actually returns 0.01 percent, the plan choice can be wrong. Statistics on filter columns still matter in vector search.

In Practice

Build a plan review workflow:

  1. Capture the canonical SQL for each retrieval route.
  2. Run EXPLAIN with representative tenant sizes and filter combinations.
  3. Compare indexed approximate search against exact search on a bounded dataset.
  4. Store a small query set for regression checks.
  5. Review plans after schema changes, statistics changes, pgvector upgrades, and model migrations.

Use route-specific examples. A support-search route with strict tenant filters is different from a recommendation route over a public catalog.

flowchart TD
    Query[retrieval SQL] --> Explain[EXPLAIN review]
    Explain --> Access[index or scan]
    Explain --> Filters[filter placement]
    Explain --> Limit[top k behavior]
    Explain --> Estimates[row estimates]
    Access --> Tune[tuning decision]
    Filters --> Tune
    Limit --> Tune
    Estimates --> Tune

For production, log enough context to reproduce slow plans without logging sensitive chunk text. Store query class, tenant tier, model version, filter counts, limit, index settings, and latency bucket.

Where It Breaks

Failure modePlan smellResponse
No vector index usedSequential scan and sort on distanceCheck index, operator class, query form, and table size
Filter removes candidatesRows removed by filter or under-returnTune search breadth, partial index, or partitioning
Wrong operator classIndex exists but ignoredMatch distance operator to index definition
Missing LIMITLarge sort or full scan behaviorUse top-k form for retrieval
Bad row estimatesPlanner picks surprising pathAnalyze table and improve statistics on filters
Generic prepared planGood for one tenant, bad for anotherTest tenant-size skew and plan caching behavior

Security and Tenancy Notes

EXPLAIN can expose table names, index names, predicates, and sometimes parameterized query structure. It should not expose chunk text unless EXPLAIN ANALYZE or logging is wrapped carelessly around application data. Keep plan capture sanitized.

For tenancy, the plan must include the tenant and authorization filters. A fast plan without those filters is irrelevant and dangerous. Every performance test should include a negative authorization case.

Cost Notes

Bad plans are cloud bills. A sequential scan over embedding rows can burn CPU and I/O while returning ten chunks. A plan that misses the intended index may force a larger instance, more replicas, or a premature move to another vector database.

Before buying new infrastructure, prove the query is written in an indexable form, the right index exists, statistics are current, and filters are not defeating candidate selection.

Observability Notes

Track:

  • Plan shape for canonical retrieval queries.
  • Index usage counts where available.
  • Query latency by tenant tier and filter combination.
  • Rows returned versus requested limit.
  • Empty-result and under-return rate.
  • Table and index size.
  • Autovacuum and analyze recency.
  • Slow queries with route labels.

For high-risk routes, keep a small plan snapshot in version control or private runbooks. The goal is to notice when a migration changes retrieval behavior.

Backup, Restore, and DR Notes

After restore, query plans may change because caches are cold, statistics are stale, indexes were rebuilt, or the restored dataset differs from current production. Include representative EXPLAIN checks in restore validation.

If a logical restore rebuilds vector indexes, run both performance and correctness checks before re-enabling full traffic. A restored database that scans every vector row may be available but not operationally healthy.

Decision Checklist

  • Does the query use the expected vector index?
  • Does the distance operator match the index operator class?
  • Is the ORDER BY expression written in an indexable form?
  • Is LIMIT present and appropriate for the route?
  • Are tenant, lifecycle, and entitlement filters in the SQL?
  • Do small, medium, and large tenants get acceptable plans?
  • Are row estimates close enough for filter columns?
  • Is exact search available as a recall baseline?
  • Do plan checks run after index, statistics, and pgvector changes?
  • Can slow plans be reproduced without exposing sensitive text?

What to Do Next

Problem: A pgvector query looks like ordinary SQL, but wrapped distance expressions, selective filters, or stale statistics can silently push the planner off the intended index — and the first symptom is a vague “pgvector is slow” ticket.

Solution: Read the plan like a database engineer before tuning anything: confirm the index is used, the ORDER BY expression is written in indexable form, filters aren’t defeating candidate selection, and row estimates match reality.

Proof: EXPLAIN on the canonical retrieval query for small, medium, and large tenants shows the expected index scan, not a sequential scan plus sort.

Action: This week, capture EXPLAIN output for the slowest retrieval route with representative parameters, and confirm the ORDER BY expression exactly matches the index’s operator class.

Sources to Verify