skip to content

Why does selecting totalCount on a connection cost a second, more expensive query?

level: middleimportance: must knowfreq 58%

answer

  1. Compare where each read stops
  2. One read is bounded, the other is not
  3. Cost scales with matches, not page size
  4. One statement saves a trip, not the work
  5. Opt-in only if the resolver is lazy

basics

~20 s

The page read stops after one page of rows; the count must visit every row matching the filter, so its cost scales with the match set, not the page. Selecting the field is what makes a client pay for it.

solid answer

~50 s

Fetching a page is a bounded read: apply the filter, order by the cursor key, stop after `first + 1` rows. An index can satisfy that and quit early, so the cost is independent of how many rows match. An exact count applies the same filter with no limit and must visit **every** match, so its cost grows with the size of the match set — typically orders of magnitude more work than the page it decorates. Folding the count into the page read with a window aggregate does not help; it still visits every match, just in one round trip. GraphQL's saving grace is that execution only resolves fields present in the selection set, so a client that omits `totalCount` pays nothing — **provided** the count is computed inside the count field's own resolver rather than eagerly when the page is built.

code

pseudocode · 14 lines
pseudocode
# eager: every request pays for the count, selected or not
resolve translationUnits(project, args):
    rows  = query("SELECT ... WHERE filter ORDER BY key LIMIT 26")
    total = query("SELECT count(*) ... WHERE filter")   # always runs
    return Connection(rows, total)

# lazy: the count runs only if the field is selected
resolve translationUnits(project, args):
    rows = query("SELECT ... WHERE filter ORDER BY key LIMIT 26")
    return Connection(rows, countThunk = () ->
        query("SELECT count(*) ... WHERE filter"))

resolve TranslationUnitConnection.totalCount(conn):
    return conn.countThunk()      # invoked only when selected

go deeper

for a junior

Recall that a count has no limit to stop at, so it reads every matching row, while a page read stops after one page. Know that a field you do not select is a field the server does not resolve.

for a middle

Explain both cost curves precisely, why a window aggregate saves a round trip but not the scan, and why the count must live behind its own resolver for selection to make it optional.

for a senior

Be ready to diagnose the eager-count implementation from production evidence: flat list latency that ignores the document shape, and backend statements issued for fields nobody selected.

for a principal

Frame it as who pays. A field that is cheap to select and expensive to serve pushes cost onto the platform; decide whether the default is off, capped, or budgeted, rather than leaving it to each client's habits.

## Two reads with completely different cost curves Take a translation-memory graph and a screen that lists German fuzzy matches in a project of 4,318,772 translation units, of which 218,431 match the filter. The **page read** is bounded work. Apply the filter, order by the cursor key, take 26 rows (25 for the page and one extra so the server can say whether another page exists), stop. With a suitable index the storage engine walks 26 index entries and returns. It does not matter whether 218,431 rows match or 4,000,000 do — the read stops at the same point. Measured on this workload it lands around 38 ms. The **count read** is unbounded work. `count of rows where filter` has no `LIMIT` to stop at: every matching row, or at minimum every matching index entry, must be visited to be tallied. Its cost is a function of the match-set size, so it grows exactly as your data grows and exactly as the user's filter becomes *less* selective. On the same workload it lands around 1,447 ms — roughly thirty-eight times the page it is decorating, for one integer. That asymmetry is the whole answer, and it is why an exact count is usually the most expensive thing on a connection. Everything else in the response is proportional to the page; the count is proportional to the corpus. ## "Can't you get it in one query?" A common suggestion is to compute the count alongside the page with a window aggregate — emitting the total on every returned row — or to run both statements in one round trip. This is worth understanding precisely: it **saves a round trip and gives you one consistent snapshot, but it does not save the work**. The engine still visits every matching row to produce the total; you have merely stopped paying network latency twice. If the count was 1,447 ms of scanning, it is still roughly that inside the combined statement, and now it is on the critical path of the page instead of beside it. The genuine benefit of the single-statement form is consistency. Two separate reads see two snapshots, so a burst of imports between them can produce a page and a total that disagree. Whether that matters is a product question; for a results headline it almost never does. ## Why selection is the lever GraphQL gives you Here GraphQL has a real structural advantage over an endpoint that returns a fixed body. Execution collects the fields named in the operation's selection set and resolves those; a field the client did not select has no resolver invocation at all. So `totalCount` costs nothing on the requests that do not ask for it. In a real app most requests do not: the first page of a list wants the headline number, and every subsequent "load more" wants only the next 25 rows. ```graphql # first request — pays for the count once query FirstPage { project(id: "prj_7742") { translationUnits(first: 25, filter: { targetLocale: "de-DE", status: FUZZY }) { totalCount edges { cursor node { id sourceText } } pageInfo { hasNextPage endCursor } } } } # load more — no count selected, no count executed query NextPage($after: String!) { project(id: "prj_7742") { translationUnits(first: 25, after: $after, filter: { targetLocale: "de-DE", status: FUZZY }) { edges { cursor node { id sourceText } } pageInfo { hasNextPage endCursor } } } } ``` ## The trap that throws the advantage away Opt-in only works if the count is computed **in the count field's own resolver**. A very common implementation builds the connection eagerly — the field resolver for `translationUnits` runs the page query *and* the count query and returns a fully populated object, whose `totalCount` resolver then just reads a property. That code pays 1,447 ms on every request in the list, including all the "load more" calls that never mention the field. The symptom is unmistakable in production: p99 latency on a list endpoint that does not improve when clients stop asking for the total. There are two standard fixes. Resolve lazily: return a connection object holding a closure or promise that runs the count only if something asks for it. Or look ahead: inspect the operation's selection set in the parent resolver and skip the count when the field is absent — the same technique used to decide which joins a page query needs. Either way, the rule to state in an interview is that **the cost must live behind the field, or the field's optionality buys you nothing**. One more consequence worth naming: because the count is a field like any other, a connection nested under a list of parents multiplies it. Fifty projects on a dashboard, each with a counted connection, is fifty unbounded count reads unless they are batched or the field is left unselected.

  • A team moves the count into the page statement as a window aggregate. What does that actually buy them?
    One round trip instead of two, and a single consistent snapshot so the page and the total cannot disagree. It does not reduce the work: the engine still visits every matching row to produce the total. Worse, the count is now on the critical path of the page, so a request that did not select totalCount pays for it anyway unless the server builds a different statement for that case.
  • Latency on a list stays high even after clients stopped selecting totalCount. What do you look at first?
    Whether the count is computed eagerly when the connection is built rather than inside the totalCount resolver. If the parent resolver always issues the count query, selection buys nothing and the timing will be flat regardless of the document. Confirm by logging the backend statements for a request that omits the field; the fix is a lazy thunk or a selection-set check in the parent resolver.
  • Does an index on the filter columns make an exact count cheap?
    It makes it cheaper, not cheap. A covering index lets the engine tally index entries instead of visiting rows, which can be several times faster, but it still walks one entry per match, so the cost still scales with the match set. An index changes the constant; only a cap, an estimate, or not counting at all changes the growth curve.

Reading a page is opening a filing drawer and pulling the first 25 folders; the exact count is pulling every folder in the cabinet to tally them, then putting them all back.

saying these in an interview costs you the question

  • Assuming a count is cheap because it returns one number
  • Believing a window aggregate removes the scan
  • Computing the count eagerly when building the connection
  • Thinking an index makes an exact count constant-time
  • Treating the count's cost as proportional to page size

context

resolving links…