The shape of the problem

A merchant-review API had to return, for one merchant, the merchant itself and several lists that hang off it: documents, verification attempts, contacts, notes and so on. Each list is a one-to-many relation from the merchant, and none of them depend on each other.

The original query fetched everything in one statement by joining every relation to the merchant.

select m.*, d.*, a.*, c.*, n.* -- and five more
  from merchant m
  left join document             d on d.merchant_id = m.id
  left join verification_attempt a on a.merchant_id = m.id
  left join contact              c on c.merchant_id = m.id
  left join review_note          n on n.merchant_id = m.id
  -- ...
 where m.id = $1;

Rows multiply, they do not add

Joining a parent to two independent children gives one row for every pair of children. Join to a third and you get every triple. The result is the product of the list sizes, not the sum.

Take a merchant with 4 documents, 6 attempts, 3 contacts, 10 notes and 5 of something else. The screen shows 28 items. The query returns 4 × 6 × 3 × 10 × 5 = 3,600 rows, and the application de-duplicates them back down to 28.

documents   4  ----+
attempts    6  ----+
contacts    3  ----+---->  needed:   4 + 6 + 3 + 10 + 5 =    28
notes      10  ----+       returned: 4 x 6 x 3 x 10 x 5 = 3,600
other       5  ----+
Sum versus product, for one merchant.

Queries like this look fine in development, where test records tend to have one of everything and 1 × 1 × 1 is still 1. They hurt with real data, and they hurt most on the records with the longest history.

The fix: one grouped read per relation

Each relation gets its own query, filtered by the parent keys. The database returns exactly the rows the screen needs, and the application groups them by merchant.

select merchant_id, id, kind, status
  from document
 where merchant_id = any($1);

select merchant_id, id, provider, status, created_at
  from verification_attempt
 where merchant_id = any($1);
Map<Long, List<Document>> docs = documents.findByMerchantIds(ids).stream()
    .collect(Collectors.groupingBy(Document::merchantId));

The number of round trips is the number of relations, fixed, regardless of how many merchants are on the page. That is the difference from the classic N+1 problem, where the count grows with the data.

Result

The review API went from minutes to seconds. The same pass also removed 46 redundant database calls from each verification job run, which is a different story and deserves its own post.

What I check first now

  • Compare rows returned to rows rendered. A large ratio is the tell.
  • Run EXPLAIN (ANALYZE, BUFFERS) against a realistic record, not a fixture with one of everything.
  • Be suspicious of any query that joins more than one independent one-to-many relation.

← All posts