Why your RAG search is slow, and where the seconds really go

Direct answer

When a RAG answer takes many seconds, the time usually goes to retrieval and data access, before the model sees a single token. Common causes are queries that recompute expensive values for every row, applications that send huge ID lists back to the database, one storage or database call per item, and an undersized or poorly tuned vector index. Time each stage separately, push filtering into the query, batch per-item work, and size indexes for the hardware.

Measure each stage before changing anything

One total for “retrieval” hides where the time went. Break the request into stages (embedding, each search channel, enrichment, signing file URLs, generation) and time each one. For SQL, run EXPLAIN (ANALYZE, BUFFERS) on the exact query the app sends, with the same filters a real request uses.

Measure where the app runs, too. In one regulatory question-answering assistant, a database round trip from a developer’s machine over a VPN took about 265 ms, against 1 to 2 ms in the cloud region. Local traces showed 6 to 20 seconds of “enrichment” that was mostly network transfer and in-process work. The SQL behind it ran in milliseconds.

In the production issues we reviewed across about 25 projects in October, RAG and database performance problems appeared in three.

What we measured

  • The same assistant’s keyword channel took 12.4 seconds on its worst test question. The full-text index returned about 8,000 candidates in 0.4 seconds. Prefix matching then forced a recheck that rebuilt each row’s search document from the full text (5.2 seconds), and ranking rebuilt it again for the survivors (6.8 seconds). A title search channel took 8.5 seconds for the same reason. The prepared fix stores the search document in its own indexed column, maintained on write and switched on by a flag that can be turned off instantly.
  • A regex match on citation identifiers had no usable index, so Postgres scanned all of roughly 877,000 rows in 3.5 seconds. The prepared fix adds trigram indexes and needs no code change.
  • Vector search took 1.7 to 1.9 seconds. The IVFFlat index used 100 lists and searched one per query, was 6.9 GB on a server with 8 GB of RAM and sat on a 500 IOPS disk. The prepared fix is an HNSW index over half-precision vectors, estimated at about 2 GB.
  • An audit of the same assistant found that its chat page, on load, sent a query with 30,752 IDs in an IN list: 1,230,167 bytes, about 99.5% of the captured log. The app loaded every accessible ID into Node, then sent them all back. One query with an EXISTS subquery returns the same categories without the list.
  • An insurance quoting platform built list pages by fetching and signing each record’s files in a loop. The team’s own estimate was 5 to 10 seconds for 100 quotes; running the calls concurrently brought the estimate to 100 to 200 ms.

Push filtering into the database

If your code fetches IDs, then sends them back as a giant IN (...) list, the database is doing a join through your application. Write the join, or use EXISTS against the table that holds the permissions. When the IDs really come from outside, pass them as one array parameter with = ANY($1), or load them into a temporary table and join it. Query size should track the result, whatever the number of records a user can access.

The same goes for precomputing. If a query rebuilds a text-search document or calls a function per row on every request, store the computed value and index it.

Choose the vector index for your data and hardware

The pgvector README says HNSW has a better speed-recall tradeoff than IVFFlat but builds more slowly and uses more memory, and that IVFFlat should be built after the table has data, with lists and probes chosen for your row count. Probes default to 1, so one list is searched unless you change it. Check that the index fits in RAM; on a slow disk, every cache miss is a wait. Compare top results between old and new indexes on real questions before dropping the old one.

Batch per-item calls and keep tables vacuumed

Generate file links in one call where the provider supports it. Supabase Storage has createSignedUrls, which creates multiple signed URLs at once.

High-churn tables need vacuum tuning. The PostgreSQL vacuum settings set autovacuum_vacuum_scale_factor to 0.2 by default, so a table must change by about 20% before autovacuum runs, and the docs allow overriding it per table. A cloud API for a robot fleet lowered the scale factors on its busiest alerts table so autovacuum would pick that table up before dead rows slowed it down.

Our articles on private RAG data architecture and keeping RAG data fresh cover the data design around these queries.

Related Zenveus services: the AI Cost & Reliability Audit and Elastic Infrastructure

Scroll to Top

Optional tracking stays off until you enable it. You can change your choice at any time.

Essential — always active
Security, requested features, and remembering these choices.

Read our cookie policy