A slow order list: EXPLAIN, indexes and pagination

Measure the request, then its individual parts

An illustrative portal displays fifty orders but takes several seconds to respond. The delay may come from the query, waiting for a database connection, looking up a customer for every row or transferring a large result. Before adding an index, record total request time, query count and query duration. Fifty is the example page size, not a recommended limit for every system.

Use filters and data volumes resembling production. A query against an empty development database cannot show what happens for a customer with years of history. Distinguish cold and warm caches and compare equivalent conditions. An average alone can hide a problem affecting only a large tenant or a busy period.

EXPLAIN ANALYZE executes the query

In PostgreSQL, EXPLAIN shows the plan the database intends to use. EXPLAIN ANALYZE also executes the query and adds observed row counts and timings. Data-changing statements therefore have effects; functions can also give an apparently read-only query side effects. Start diagnosis in a suitable test environment and define an execution budget and acceptable load before running it in production.

Compare estimated and observed rows, inspect node repetition and the number of discarded rows. A large discrepancy may justify checking statistics or uneven data distribution. A sequential scan is not automatically a defect: reading a large portion of a table can be more suitable than many random reads through an index.

Design the index around filtering and ordering

For one tenant's orders sorted by creation time, a B-tree index on tenant_id, created_at and id can be a candidate. Tenant equality restricts the range while the remaining columns match the order. Confirm its use and value through the query plan and measurement; both depend on data distribution and the query. An additional filter such as order status may justify a different design.

Every index also takes space and requires maintenance during writes. Do not create every filter combination just because its fields appear in a form. Choose significant production queries, compare candidate indexes and check insert and update times too. Creating an index during normal operation is a separate change with its own locking and recovery rules.

Give the cursor a unique, stable ordering

A large OFFSET can be expensive because the database still processes skipped rows. For sequential browsing, the final displayed created_at and id pair can replace a page number. This example assumes non-null values, an immutable creation time and a unique id. Both columns are compared and sorted in the same descending direction.

The cursor must also retain the original filter. The server validates its format and scope and derives the tenant again from authorised context. New orders ahead of the cursor do not appear during continuation; changing filters or sorting should start a new traversal. This approach does not produce a consistent historical snapshot. An export needing that property requires a separate agreement on isolation and read lifetime.

SELECT id, created_at, status
FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Even a small result can cause N+1 queries

The application loads one page of orders and then queries each row's customer name separately. An index does not remove the network waits caused by dozens of additional queries. Depending on the data model, a JOIN or batched relationship lookup can help. Joining multiple one-to-many relationships can also enlarge the result; mechanically joining every table is insufficient.

A list often does not need every order item, attachment and long description. Return the fields actually displayed and load details separately. After optimising, inspect query count, response size and effective permissions. A batch lookup must not widen the tenant scope previously enforced by each individual query.

Check correctness during concurrent changes

Combine performance measurement with correctness checks. Prepare several orders with identical creation times, a large tenant and a new insert between page requests. Verify that ordering remains stable and continuation matches the documented contract. Users should understand whether they are browsing a live list or receiving a fixed export.

• When created_at is equal, id decides the order and makes the page boundary unique.
• Changing a filter discards the previous cursor rather than mixing result sets.
• Latency and query count are measured for a large tenant's traversal.
• The new index keeps significant write operations within their agreed timing budget.

Put the topic into practice.