Slow API Endpoints? Start with PostgreSQL Query Plans
TL;DR
One slow location-search SQL batch went from about 1.56 seconds to 69 ms—95.6% less time—with the same results. The decisive change was giving PostgreSQL better statistics for an expression it already had indexes to search.
Other fixes reduced the amount of data fetched and moved repeated alias processing out of requests. The requirement throughout was simple: make the existing API faster without changing its behavior.
| Measured work | Before | After | Less time |
|---|---|---|---|
/places/suggest: exact-location SQL batch, DEV |
~1,560 ms | 69.325 ms | ~95.6% |
/places/suggest: prefix SQL, limit 8, warm DEV database |
224.196 ms | 85.350 ms | 61.9% |
/centers/nearby: settlement lookup, local synthetic data |
306.092 ms | 3.481 ms | 98.9% |
These are component measurements, not end-to-end HTTP timings or production percentiles. The first comparison retained 4,932 rows and 52 resolved identities. The synthetic test used 50,012 settlements and 140,016 aliases, with an application-cache miss and warm PostgreSQL buffers. Reductions use (before − after) / before.
Public-example note: endpoint, service and database-object names are anonymized. The measurements and technical behavior are unchanged.
The investigation started with a location dropdown taking seconds. A monitoring screenshot showed averages of 9.8 seconds for search and 7 seconds for nearest hubs, but from only three and two requests. That justified investigating; it was too little evidence for a reliable baseline.
Here is the PostgreSQL optimization workflow I would reuse for the next slow API endpoint.
Jump to request timing, query plans, statistics and indexes, or troubleshooting.
1. Measure the request before changing the query
Capture one reproducible request: parameters, locale, authentication context, response body, timestamp and request ID. Keep the response for comparison later.
For a public endpoint, a starting point is:
curl -sS -G 'https://api.example.com/places/suggest' \
--data-urlencode 'q=sa' \
--data-urlencode 'country=FR' \
--data-urlencode 'limit=8' \
-D /tmp/place-suggestions.headers -o /tmp/place-suggestions.json \
-w 'HTTP=%{http_code} TTFB=%{time_starttransfer}s TOTAL=%{time_total}s\n'
Replace the example URL and parameters with your API. Compare client timing with the server trace for that request.
| Layer | What to measure |
|---|---|
| Client | Time to first byte and total elapsed time, including the network |
| Application | Request duration, connection checkout, external calls, rendering |
| Database | Query count, duration, rows returned and execution plans |
| Runtime/cache | Allocations, garbage collection, cache calls and payload size |
In one later pair of real search requests, Rails X-Runtime dropped from 1.004 s to 0.335 s while client total dropped from 1.323 s to 0.809 s. Some variation was inside the application boundary. Faster transfer alone could not explain it.
That still did not prove a particular cache—or garbage collection—caused the difference. Record the serving worker and measure several distinct queries as well as repeats. A fresh worker, an application-cache miss and a cold database are different test conditions.
2. Find the real query and read its plan
Capture the SQL generated by the application, including bound values. A Rails endpoint may use several databases; inspecting the primary database will not explain a query running against an external geography database.
Verify the connection and inspect the relevant indexes. These examples use anonymized geography relation names; substitute your own:
SELECT current_database(), current_user;
SELECT indexrelid::regclass AS index_name,
indisvalid, indisready,
pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'public.place_catalog'::regclass;
SELECT last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND relname = 'place_catalog';
Also check connection checkout and pg_stat_activity wait events. A request waiting for a connection or a lock needs a different fix from a query scanning too many rows. A privileged DBA may be needed to see other sessions.
Start with plain EXPLAIN. When execution is appropriate, use a bounded EXPLAIN ANALYZE on a representative local copy or an approved environment. For example, this is the lookup against the prepared alias table discussed below:
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '1s';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT name, country_code, region_code, lat, lng, population, capital
FROM public.place_aliases
WHERE country_code = 'US'
AND search_name LIKE 'bodega%'
AND lat BETWEEN -90 AND 90
AND lng BETWEEN -180 AND 180
AND population >= 0
ORDER BY population DESC, name_sort_rank DESC, source_ordinal ASC
LIMIT 1;
ROLLBACK;
EXPLAIN ANALYZE runs the statement. Use a known read-only query, review called functions and bound the work; a read-only transaction does not make an expensive scan cheap. If it times out in an interactive session, roll back the aborted transaction.
Read the plan for:
- Estimates versus actual rows: find the earliest large mismatch.
- Loops: a cheap operation repeated thousands of times may dominate.
- Buffers and spills: inspect reads, hits and temporary sort/hash work.
- Filtering: determine how much data is read and then discarded.
A sequential scan can be the right plan for a broad query. Plan costs are not milliseconds, and parent-node times include child work. PostgreSQL explains these distinctions in its EXPLAIN guide.
3. Fix estimates before adding another index
PostgreSQL’s planner chooses how to run a query using estimates of the data it will encounter. An index can exist while those estimates still lead to an expensive plan.
Our /places/suggest batch scanned roughly 3.9 million source rows. Statistics were old, but ordinary ANALYZE alone did not resolve it: the planner still estimated about 731,280 matches where 4,932 were returned.
The missing piece was statistics for the exact English-name expression across the relation. Statistics attached to the partial expression index did not supply the estimate this batch needed.
The essential change looked like this:
-- Database-owner operation, after confirming this statistics object is absent.
CREATE STATISTICS public.st_place_catalog_english_name
ON (lower(jsonb_extract_path_text(tags, 'name:en')))
FROM public.place_catalog;
ANALYZE public.place_catalog;
Creating the object defines what to measure; ANALYZE collects samples. This changed the plan to use existing indexes and reduced the observed batch time to 69.325 ms, preserving all 52 resolved identities. PostgreSQL expression statistics.
This is the core operation, not a production-ready provisioning script. Our owner script also verifies the relation, ownership and any existing definition, with timeouts. Neither statistics changes nor index builds belong inside a user request.
Why is PostgreSQL not using my expression index?
Inspect the actual expression, not just the indexed column’s name. These two expressions produced equivalent values for our lookup, but were not interchangeable for index matching:
- Runtime:
lower(jsonb_extract_path_text(tags, 'name:en')) - A differently defined index:
lower(tags ->> 'name:en')
Check the operator class, collation, partial predicate and validity too. PostgreSQL needs to recognize that the query can use the index; a partial index also requires a compatible query condition. See expression indexes and partial indexes.
One detour was testing explicit VARIADIC syntax. It was not the fix: both forms could use the simple index while the batch estimates remained wrong. A faster repeat was not enough to establish causality.
4. Reduce the work before fetching the payload
LIMIT 8 can still process thousands of candidates
The /places/suggest prefix query fetched wide JSON tags before reducing its candidates. We selected narrow keys first, applied the existing eligibility and ordering rules, then fetched the required payload. Together with the supporting covering index, warm prefix SQL fell from 224.196 to 85.350 ms.
Moving a limit is safe only when it preserves the same winners. Duplicate or missing IDs and ties at the cutoff needed explicit handling. Ambiguous cases retained the old query path; adding an arbitrary tie-breaker would have changed API behavior.
The transferable idea is to reduce rows and row width before expensive decoding, transfer and rendering. Keep correctness checks around the cutoff.
/centers/nearby was slow before it even searched for hubs
Settlement discovery expanded JSON names, localized aliases and semicolon-separated variants during requests. The existing hub-table index already served the observed hub reads. Another hub index would have targeted the wrong work.
The geography-data owner prepared a plain alias table: one row per normalized name, with country, coordinates and ranking fields. The API could then:
- Use the country/prefix index to find matching aliases.
- Select the same eligible settlement using the original ranking and tie rules.
- Use its coordinates to find nearby hubs.
This produced the 98.9% reduction in the local settlement-lookup fixture. The prefix index does not eliminate all ranking work: broad matches can still require sorting many candidates.
Precomputation helped because the same normalization had been repeated. Its owner must keep the lookup synchronized when the source changes.
5. Add caching only after the miss is understood
After SQL improved, /places/suggest still built around 1,300 suggestions to return eight for one France query. It repeatedly resolved shared subdivision and display labels. Request-local reuse removed repeated work; prepared reference lookups reduced work further.
Before adding Redis, ask:
- Can this lookup be reused within the current request?
- Can reference data be prepared before users need it?
- Can the cache hold the selected result instead of a large source payload?
- Which locale, country, filters and source changes affect the key or invalidation?
- What happens on a miss, an empty result and a source failure?
Our nearest-hub response cache still read and fingerprinted the relevant hub data. A cache hit did not mean zero SQL. Cache reads and payload bytes deserve measurement too.
For a dropdown used once before moving to the next screen, repeated-request speed is insufficient. Test distinct queries and the first request in a new process. Profile allocation and GC if database time no longer explains the request. Do not flush shared production caches to manufacture a cold test.
We also identified unnecessary lifecycle complexity around city reference data. Bundling a fixed lookup into the application image is a separate planned simplification; it is not an implemented result claimed here.
6. Verify the API and the deployed path
Compare complete ordered responses before and after: identities, labels, locale behavior, duplicate names, NULL handling, empty results and errors. For location selection, test saving a returned choice and reading it back as well.
Our later repository verification recorded 24,644 examples, zero failures and four existing pending examples. That supports the checked implementation; it does not establish a production latency percentile. The same distinction matters when optimizing a Rails test suite: faster execution must retain the behavior being checked.
Deployment uncovered another practical failure: the alias table existed, but the application’s database role lacked SELECT access and the new reader flag was off. Verify with the actual runtime role and effective serving-process configuration, then measure the endpoint again.
Keep ownership explicit: application code belongs in the backend release; external geography indexes, statistics and source maintenance belong with that database’s owner. Record application instructions and rollback in the PR.
For production index changes, CONCURRENTLY avoids blocking ordinary writes, but can still wait and consume substantial IO. It cannot run inside a transaction block, and a failed build can leave an invalid index. IF NOT EXISTS checks a name, not an equivalent definition. Inspect the result. Concurrent index builds.
Troubleshooting: symptom → next check
| Symptom | Next check | What helped or matters |
|---|---|---|
| Index exists; query is slow | Estimated versus actual rows; exact expression and predicate | Relation-level expression statistics fixed our search batch. |
ANALYZE did not improve the plan |
Whether statistics cover the expression being filtered | Refreshing unrelated samples is insufficient. |
| Small response; large SQL cost | Rows processed, row width, sorts before LIMIT |
Narrow candidates first; retain ranking and ties. |
| SQL is fast; API is slow | Query count/N+1, pool wait, cache IO, allocations and rendering | Avoid repeated work; measure the remaining stage. |
| First request is much slower | Worker identity, initialization and each cache layer | A fast repeat does not prove a fast miss. |
| DEV works; deployed endpoint does not | Actual DB role, relation access, reader setting and image | The table existing was only one prerequisite. |
| Performance regresses after a source rebuild | Index/statistics definitions and fresh samples | Ordinary refresh and DROP/recreate need different maintenance. |
Common questions
Should I add an index or a cache first?
Capture the plan and request breakdown first. Poor estimates, repeated queries and connection waits require different fixes. A cache may help repeated requests while leaving the slow miss intact.
Should Rails queries use ActiveRecord, Arel or raw SQL?
Use ActiveRecord/Arel for normal composition and bound values, then inspect the generated SQL. Switching syntax alone does not reduce database work. PostgreSQL-specific expressions remain PostgreSQL-specific even when constructed with Arel; keep owner DDL separate from application queries.
Can I promise the same response time on every request?
No. Measure a representative distribution across queries and concurrency, including first-process and cache-miss cases. Record p50/p95/p99 when the sample supports them. A warm SQL benchmark cannot promise a particular end-to-end API latency.
For the next endpoint, keep a small evidence record: one reproducible request, its timing breakdown, the actual SQL and plan, one justified change, and response-parity plus timing results. That makes the investigation reusable instead of starting again with “maybe add a cache.”