A Small Result Set Can Hide a Huge Query

A query returned a small batch, but spent most of its time deciding which rows to exclude. Fixing that repeated work made the workflow much faster. Then another profile exposed a different cost: an insert path could write no rows while repeatedly checking records that already had state.

Both findings came from the same habit: measuring the work beneath the result. They also raised a useful question. If another round can find another improvement, when is a performance fix actually done?

A measured improvement proves something about a particular workload. It does not prove that the system is optimal. Completion needs an explicit scope and acceptance criteria; otherwise optimization can become an endless sequence of plausible rewrites.

Updated September 8, 2026 to include the second investigation and a practical stopping rule.

This article generalizes the investigations. Product names, infrastructure details, identifiers, workload sizes, and exact measurements are deliberately omitted. All SQL uses fictional schemas and simplified requirements. It explains decisions to evaluate, rather than providing a production patch or reproducible benchmark.

What was the anti-pattern?

The first requirement was ordinary: find due work without an active attempt, return a small batch, and preserve a stable order.

SELECT w.id
FROM work_items AS w
WHERE w.due_at <= CURRENT_TIMESTAMP
  AND NOT EXISTS (
    SELECT 1
    FROM attempts AS a
    WHERE a.item_id = w.id
      AND a.status IN ('queued', 'running')
  )
ORDER BY w.due_at, w.id
LIMIT 20;

This is reasonable SQL. NOT EXISTS is not inherently slow, and a nested loop can be excellent when each inner lookup is cheap. The problematic execution strategy behaved approximately like this:

For every refill:
    For each candidate examined:
        Revisit a large portion of the active attempts
        Decide whether to exclude the candidate
    Return a small batch

If each refill examines C candidates and each candidate scans A active attempts, that part of the work can approach C × A. R similar refills add another multiplier. Early exits, data distributions, and alternative plans change the cost: this describes one bad physical strategy, not the complexity of NOT EXISTS as a language construct.

The name for the broader problem is work amplification: excessive internal work relative to the useful outcome. Calling it application N+1 would blur an important distinction. These repeated operations happened inside the database, without requiring an extra network round trip for each candidate.

Why the obvious signals were misleading

Four reassuring observations did not establish that the query was efficient:

• A small LIMIT. PostgreSQL may examine and reject many candidates or process substantial sorting input before returning the requested rows.
• An index scan. The plan may visit many entries and apply the selective comparison as a filter afterwards. An Index Cond and a post-retrieval filter describe different work.
• A warm cache. Avoiding a disk read does not remove repeated page visits or comparisons.
• A passing small test. A fresh dataset may conceal the plan sensitivity that appears with a burst, accumulated history, or many equal due times.

PostgreSQL’s EXPLAIN guide explains how these plan details relate. The investigation needed actual execution evidence, rather than confidence in the query’s appearance.

The solution: remove repetition and align access paths

The first improvement combined an access path matching the requested order with cheaper active-membership evaluation.

For the fictional schema, these are two indexes to evaluate:

CREATE INDEX work_items_due_order_idx
    ON work_items (due_at, id);

CREATE INDEX attempts_active_item_idx
    ON attempts (item_id)
    WHERE status IN ('queued', 'running');

An index on (due_at, another_column, id) does not generally provide ORDER BY due_at, id when the middle column varies. A matching order can avoid sorting, but does not ensure that few rows will be examined if exclusion rejects many candidates. See Indexes and ORDER BY.

The partial index narrows the indexed attempt population to the relevant statuses. It adds write and storage costs, and the planner must be able to establish that the query implies its predicate. Partial Indexes describes that requirement.

Another query shape evaluated membership without correlating the subquery to each outer candidate:

-- Preconditions: work_items.id and attempts.item_id are NOT NULL.
SELECT w.id
FROM work_items AS w
WHERE w.due_at <= CURRENT_TIMESTAMP
  AND w.id NOT IN (
    SELECT a.item_id
    FROM attempts AS a
    WHERE a.status IN ('queued', 'running')
  )
ORDER BY w.due_at, w.id
LIMIT 20;

In the investigated case, the selected plan built a hashed membership set once per query, avoiding the repeated broad inner scan. The evidence supported that combination for the tested workload.

It did not establish a rule to replace NOT EXISTS with NOT IN. Either spelling may lead to a suitable plan. They also have different behavior around NULLs: the non-null preconditions above are essential. See PostgreSQL’s Subquery Expressions.

The membership set still consumes memory and may be rebuilt on every refill. A bounded active set matters; a successful test does not prove arbitrary-backlog safety.

The second bottleneck: zero rows inserted

After reducing the exclusion cost, the next profile showed material time in state creation. Its job was to find eligible source records without corresponding processing state and create a bounded batch of missing rows.

Many calls created nothing. Nevertheless, the plan repeatedly evaluated account and eligibility conditions for retained records before excluding records that already had state. The output was empty; the work was not.

The useful change was to establish the missing-state set before those eligibility checks. A fictional version looks like this:

WITH missing_items AS MATERIALIZED (
  SELECT s.tenant_id, s.id, s.account_id
  FROM source_items AS s
  WHERE s.enabled
    AND NOT EXISTS (
      SELECT 1
      FROM work_state AS w
      WHERE w.tenant_id = s.tenant_id
        AND w.item_id = s.id
    )
)
INSERT INTO work_state (tenant_id, item_id)
SELECT m.tenant_id, m.id
FROM missing_items AS m
JOIN accounts AS a
  ON a.tenant_id = m.tenant_id
 AND a.id = m.account_id
WHERE a.enabled
  AND processing_allowed(m.tenant_id, m.id)
ORDER BY m.tenant_id, m.id
LIMIT 20
ON CONFLICT (tenant_id, item_id) DO NOTHING;

Here, processing_allowed stands for an eligibility check. The example assumes non-null identity and ownership keys, a matching unique constraint on work_state (tenant_id, item_id), and suitable indexes for the ownership lookups. Its batch size is illustrative.

Writing a predicate earlier in SQL does not specify its evaluation order. A plain, eligible CTE can also be folded into its parent query. MATERIALIZED requests a separate calculation, which provided the needed boundary in the measured plan. PostgreSQL documents the behavior in Common Table Expression Materialization.

That boundary has a cost. It can prevent useful predicate pushdown, retain an intermediate result, and still require scanning source/state history. It belongs here because the measured tradeoff helped. It is not a general instruction to materialize every CTE.

The end-to-end improvement mattered more than a faster statement in isolation. In the larger tested workload, both the seed statement’s work and total elapsed time fell. In the smaller case, seed work fell while total elapsed time did not improve. These were controlled single-run comparisons, not a latency distribution: cache state, automatic statistics updates, and host activity were not laboratory-fixed. Reporting those limits keeps the conclusion within the evidence.

Preserve admission before optimizing it

The limit stays after eligibility. If the query limits missing records first, ineligible low-ID records can consume the batch and leave eligible records undiscovered.

Ownership keys must also remain intact through exclusion, joins, and insertion. Dropping the tenant key because an ID appears unique in a fixture would change the contract.

Finally, checking for absence is not a concurrency guarantee. The unique constraint and conflict handling protect this simplified insertion; a complete worker system still needs its existing authorization, claim, lease, and completion rules. PostgreSQL’s INSERT reference describes how conflict handling uses uniqueness enforcement.

Compatibility checks covered missing state, records with existing state, denied records preceding eligible ones, pause/resume, restored eligibility, ordering, and repeated calls. Performance work had to preserve those outcomes.

How to investigate it

Attribute time to phases before changing SQL: fixture setup, initial selection, message processing, refill, and final checks. If the entry point is a database function, inspect its nested statements as well as the outer call.

For a controlled read query, use:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

ANALYZE executes the statement. Use an isolated representative environment for mutating workflows; rollback does not universally undo external effects.

Read the plan with five questions:

1. Where do estimated and actual row counts diverge?
2. Which expensive nodes execute repeatedly?
3. How much work is discarded before a useful result appears?
4. Do the access paths support the selective comparisons and complete ordering?
5. Where does buffer activity concentrate?

Actual rows and times for repeated nodes are per-loop averages, and parent measurements can include child work. Adding every node’s counters can double-count the same execution. Shared buffer hits count accesses, not unique pages or physical reads. Use the EXPLAIN documentation to interpret those measures alongside elapsed time and CPU activity.

For comparisons, use the same logical fixture and SQL instrumentation. Capture deltas for the scenario, not cumulative counters from an earlier suite. Record whether statistics, prior fixtures, and dead rows are retained. Run competing heavy suites sequentially when measuring performance, and repeat measurements before making a stable latency claim. Host load, cache state, and planner/statistics differences can change timings; a faster unrelated run is not a controlled comparison.

What did not count as a complete fix?

Refreshing statistics improved estimates, but did not alone settle the burst case. Statistics maintenance remains necessary; it cannot be assumed to have observed every new distribution. See PostgreSQL’s bulk-loading guidance.

An index alone did not prove selective use. A correlated alternative also looked promising in an earlier test but failed to retain its advantage in the complete workload. The final implementation needed its own proof.

A larger timeout, reduced fixture, deleted assertion, or permanently forced planner setting would not demonstrate removal of the repeated work. Nor would optimizing the harness while leaving an expensive production statement intact.

The two improvements addressed different costs:

Phase	Repeated work observed	Change supported by the measurements
Due-work selection	Broad active-attempt scans for outer candidates	Align ordering and evaluate active membership more cheaply
State creation	Eligibility checks for records that already had state	Exclude existing state before those checks

Both are examples of work amplification. Neither establishes that every phase is now cheap, that CPU use disappears, or that larger workloads will scale linearly forever.

When is the optimization done?

“Optimal” requires an objective, constraints, and a meaningful set of alternatives. Another plausible rewrite is not evidence of another improvement.

Before starting another round, define the supported data envelope and the budgets that matter: latency, CPU or internal work, memory, and acceptable operational complexity. Then use a stopping rule:

• The scoped workload meets the agreed performance budget under representative conditions.
• Correctness and compatibility pass through the final integrated entry point.
• The measured failure has a regression check tied to its causal work.
• Remaining costs and untested conditions are explicit.
• Further change needs evidence of a worthwhile gain relative to its implementation and maintenance cost.

This makes “done” a bounded engineering claim. It does not require proving that no faster algorithm exists.

An additional pass might find another useful improvement. It might also find that the remaining time is spread across necessary operations, or that avoiding a scan requires new persistent state, repair paths, and invalidation rules. Those designs need a fresh comparison. They should not be adopted just to produce another version.

Reopen the work when a budget is exceeded, the workload changes, or a new profile exposes material avoidable cost. Do not reopen it merely because another optimization can be imagined.

How to prevent a recurrence

1. Review growth as well as output size. Include candidate population, active backlog, timestamp ties, retained history, and refill frequency.
2. Keep representative state in the proof. Test bursts and accumulated data, not only an empty, freshly analyzed database.
3. Budget internal work alongside time. A calibrated work budget can catch repeated scans even on a fast machine. It complements latency and correctness checks.
4. Preserve the original invariants. Eligibility, ownership, concurrency, retries, idempotency, and terminal outcomes remain requirements after optimization.
5. Qualify the final candidate. Follow the actual package command, migration runner, SQL, and callers. Evidence for an intermediate experiment does not qualify a later rewrite.

Keep routine development checks focused. Reserve the complete relevant load proof for integration and release qualification, while ensuring changes to the affected path still receive that proof. Avoid duplicate heavy runs across unrelated edits and coordinate concurrent test owners.

Interview practice: questions and answers

”A query returns only twenty rows. Why might it be slow?”

It can inspect and reject many candidates, repeat expensive inner operations, or sort a large input before returning those rows. I would inspect actual loops, filters, buffers, and ordering before proposing an index or rewrite.

”Would you change NOT EXISTS to NOT IN?”

Only after proving equivalent semantics, especially around NULLs, and comparing the selected plans. Either can be efficient. The goal is cheaper exclusion with the same result, not a preferred SQL spelling.

”An INSERT writes zero rows. Why does it use CPU?”

Its source query can still scan, join, and evaluate eligibility before finding nothing to insert. I would profile that source work and check whether irrelevant records can be excluded earlier without changing admission.

”Would you materialize the CTE?”

I would compare the work it avoids against the optimization freedom and intermediate-result cost it introduces. The boundary must help the actual workload, preserve eligibility and limits, and survive the complete workflow test.

”How do you know there is no better version?”

Usually I do not. I can show that the tested version meets a defined budget, removes the measured failure, and preserves behavior. Further optimization needs new evidence and an explicit tradeoff. That is a defensible stopping point without claiming universal optimality.

The question to carry into a review is: how much work happens beneath each useful result, how often does it repeat, and what evidence would justify changing it again?