Slow API: Investigating a Slow Query

This is the first article in Slow API, a series of practical investigations into API performance. We start with a slow SQL query; Part 2 explores N+1 queries when assembling the response.

An orders endpoint is taking longer than expected. We open a request trace: acquiring a database connection takes little time, but one SELECT accounts for most of the delay. The response contains only 50 orders.

The statement looks ordinary. It filters by tenant_id, sorts by creation time, and ends with LIMIT 50. Nothing in the SQL text explains where the time went. It could be waiting, reading more data than expected, or doing expensive work before it can return the first row.

This is the starting point for our lab investigation, not a report of a production incident. We know which query to inspect. We do not yet know what to change.

Reproduce the query before changing it

First, we need a dataset large enough to expose work that a tiny development database might hide. We will give one tenant a substantial order history, then run the same query against it.

Open psql against a local scratch database and run the SQL blocks below in the same session. The temporary table disappears when you disconnect. This exercise inserts 200,000 synthetic rows; it needs no application code or production data.

CREATE TEMP TABLE slow_api_orders (
    id bigint PRIMARY KEY,
    tenant_id integer NOT NULL,
    created_at timestamptz NOT NULL,
    total_cents integer NOT NULL
);

INSERT INTO slow_api_orders
SELECT
    g,
    CASE WHEN g % 2 = 0 THEN 42 ELSE 100 + g % 99 END,
    TIMESTAMPTZ '2026-01-01 00:00:00+00'
        + (g / 10) * INTERVAL '1 second',
    1000 + g % 10000
FROM generate_series(1, 200000) AS g;

ANALYZE slow_api_orders;

Tenant 42 owns exactly half the rows. Several orders share a creation timestamp, so the query uses the unique id as a tie-breaker. ANALYZE supplies planner statistics before we inspect execution.

This setup removes competing application transactions from the experiment. It will not reproduce every production condition, but it lets us examine the work the query itself requires.

Look inside the execution

A duration tells us that the statement took time. We need the execution plan to see what PostgreSQL did during that time:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, total_cents
FROM slow_api_orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN ANALYZE actually executes the statement. Here it is a controlled read against synthetic data. TIMING OFF removes per-node clock measurements while retaining actual row counts and total execution time.

On PostgreSQL 17.10, the plan from this lab looked like this, with costs and buffer lines omitted:

Limit (actual rows=50 loops=1)
  -> Sort (actual rows=50 loops=1)
       Sort Key: created_at DESC, id DESC
       Sort Method: top-N heapsort
       -> Seq Scan on slow_api_orders
            (actual rows=100000 loops=1)
            Filter: (tenant_id = 42)
            Rows Removed by Filter: 100000

The first useful clue is at the bottom. The scan emits 100,000 matching rows and rejects another 100,000. It has examined the entire table. Above it, the sort consumes the matching rows to choose its top 50, then the limit returns them.

Our initial assumption was that a small result would mean little work. The plan shows why that assumption fails: PostgreSQL must identify the newest orders before it can stop. LIMIT 50 bounds the output, not necessarily the input.

The top-N sort avoids fully sorting the entire result, but still needs to inspect its input. Its actual rows=50 describes output, not the number of candidates considered.

Keep two plan-reading rules in mind: planner costs are not milliseconds, and parent nodes include child work. A sequential scan can be appropriate when much of a table is needed. See PostgreSQL's EXPLAIN guide.

Form a hypothesis we can test

The table already has a primary-key index on id. That gives PostgreSQL a way to find an order by ID, but does not provide the tenant and timestamp ordering this request needs.

What if the database could go directly to this tenant's newest orders? Our prediction is specific: an index matching the filter and order should remove the explicit sort and let the scan stop after producing 50 rows.

Test that prediction with one change:

CREATE INDEX slow_api_orders_tenant_created_id_idx
ON slow_api_orders (tenant_id, created_at DESC, id DESC);

The leading key groups entries by tenant. Within a tenant, the remaining keys match the requested order, including the ID tie-breaker. This gives the planner the access path our hypothesis requires. PostgreSQL explains the relevant rules under multicolumn indexes and indexes with ORDER BY.

Run the same EXPLAIN block again. In the lab, it became:

Limit (actual rows=50 loops=1)
  -> Index Scan using slow_api_orders_tenant_created_id_idx
       on slow_api_orders (actual rows=50 loops=1)
       Index Cond: (tenant_id = 42)

Both predicted changes appear: there is no explicit sort, and the scan produces only the 50 rows consumed by the limit. This is an ordinary index scan, not an index-only scan: total_cents still comes from the table.

Check whether the evidence supports the fix

These are observations from one local PostgreSQL 17.10 run, not a production benchmark or a promised speedup:

The elapsed time fell, but that alone would be weak evidence. Index creation reads the table and changes cache state, and timings depend on hardware and settings. A single before/after timing is not a controlled performance comparison. The stronger evidence is the change in the plan: the query now does substantially less work to produce its page.

Because the fixture uses a temporary table, its data-buffer counters are labeled local. Buffer reads do not by themselves prove physical disk reads; the operating system may serve them from cache. Compare buffer activity alongside rows and plan shape.

We also need the same answer. In this fixture, the ordered IDs must be 200000, 199998, ... , 199902 before and after the index. The tie-breaker makes that order deterministic; the lab verification confirmed that the ordered IDs were unchanged.

Follow one more clue: a later page

The first page is cheaper now. Before declaring the endpoint fixed, try a request for a much later page. Predict the scan's row count before looking at the result:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, total_cents
FROM slow_api_orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

The abridged plan from the lab shows two different row counts:

Limit (actual rows=50 loops=1)
  -> Index Scan using slow_api_orders_tenant_created_id_idx
       on slow_api_orders (actual rows=50050 loops=1)
       Index Cond: (tenant_id = 42)

In the lab, the index scan now produced 50,050 rows: 50,000 to skip and 50 to return. The index still supplies the order, but a deep offset reintroduces work proportional to page depth. PostgreSQL documents this behavior in LIMIT and OFFSET.

The Limit node discards the first 50,000 rows supplied by its child. The index scan has no instruction to jump to the 50,001st matching row. Seeing an index in the plan was not enough; the work still grows with page depth.

Remember where the previous page ended

Cursor pagination works like a bookmark. We load a page, remember the values from its last row, and send those values with the next request: “Give me the next 50 orders after this one.”

Here, we sort by created_at and id, so we remember both. The timestamp tells us where we stopped in time; the ID distinguishes orders created at the same moment. Those two values form our cursor. The first page needs no cursor; each following page uses the last row returned by the previous one.

At the same depth as our offset example, the previous page ends at order 100002, created at 2026-01-01 02:46:40+00. Since both columns sort descending, we ask for values smaller than that pair:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, total_cents
FROM slow_api_orders
WHERE tenant_id = 42
  AND (created_at, id) < (
      TIMESTAMPTZ '2026-01-01 02:46:40+00',
      100002::bigint
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

The index can find that position and continue from it. Here is the abridged plan from the lab:

Limit (actual rows=50 loops=1)
  -> Index Scan using slow_api_orders_tenant_created_id_idx
       on slow_api_orders (actual rows=50 loops=1)
       Index Cond: ((tenant_id = 42) AND
         (ROW(created_at, id) <
          ROW('2026-01-01 02:46:40+00'::timestamptz,
              '100002'::bigint)))

The cursor appears in Index Cond. PostgreSQL can start scanning at the saved position without walking through the preceding 50,000 rows. In this lab, the scan emits only 50 rows, and we verified that they are the same orders returned by the offset query.

Keep both cursor values: filtering only by timestamp would skip orders that share the last row's timestamp. PostgreSQL's row comparison rules explain how the pair is compared.

The trade-off: no direct jump to page 1,000

The main drawback is that we cannot jump straight to an arbitrary page in the middle without already knowing its cursor. A page number alone does not tell us which order to continue after. We must either follow the preceding pages or have a saved cursor for that position.

That makes cursors a good fit for “Next” and “Load more” navigation. If users need to open page 1,000 directly, offset pagination supports that interaction, with the scanning cost we measured. Choose based on how the product needs to navigate, not just which query is faster.

Keep the same filters and ordering between requests. The cursor remembers a position; it does not freeze the data while the user browses.

Decide what to ship

We found two sources of unnecessary work. The original access path scanned and filtered the table, then selected the newest rows through a sort. The matching index fixed the first page. Deep offsets still walked earlier rows; a cursor let the same index resume from the previous boundary. Adopting that second change requires the pagination contract to support it.

Before applying this to a real service, check the actual query and representative parameters. A different filter, ordering, tenant size, or prepared-plan choice can produce a different plan. Include both typical requests and the slow ones.

An index adds storage and write maintenance. The lab's ordinary CREATE INDEX is appropriate for its temporary table. On a live table, plan the migration separately: a standard build blocks writes; CREATE INDEX CONCURRENTLY permits concurrent writes but has operational caveats, cannot run inside a transaction block, and can leave an invalid index after failure. See CREATE INDEX.

After rollout, compare SQL execution time and endpoint latency under comparable traffic. Check write latency and returned data as well. If the query is faster but the API is not, inspect the request trace again: this index fixes one source of work, and the next delay may be elsewhere.

The Slow API series

1. Investigating a Slow Query (this article) — Use EXPLAIN ANALYZE, improve an index, and replace deep OFFSET pagination.
2. Fast Queries, Slow Endpoint — Find N+1 queries and compare SQLAlchemy relationship-loading strategies.
3. Waiting for a Database Connection — Measure Go connection pool waits, shorten transactions, and understand where PgBouncer helps.
4. Waiting for a Database Lock — Find the blocking transaction and shorten lock waits without breaking purchase correctness.