The Query Got Faster—and the Result Became Wrong

I was optimizing a PostgreSQL report when the execution plan showed what looked like decisive evidence.

One branch of a join returned this:

Index Scan using ...
  (actual time=0.000..0.000 rows=0 loops=137415)

The node had run more than 137,000 times and displayed zero rows. The branch appeared to contribute nothing, so removing it looked like an easy win: less work, fewer joins, and no need for the DISTINCT that had been cleaning up duplicates produced by the original query.

The rewritten query was much faster.

It was also wrong.

When I compared a business total produced by the original query with the same total from the optimized version, the values did not match. The path that looked empty in the plan did contribute records—just not in the way I had inferred from that single line of output.

That mistake changed how I evaluate SQL optimizations. A faster execution plan is evidence about performance. It is not proof that two queries mean the same thing.

The optimization looked safer than it was

The original report assembled payment information through two possible relationship paths. The query combined them with COALESCE and used DISTINCT to remove duplicates.

That shape was expensive. It required PostgreSQL to scan large tables through both paths and then sort or deduplicate the resulting rows. When one side of the query appeared to return nothing, the natural hypothesis was that the second path had become obsolete or was irrelevant for the requested data.

The plan seemed to support that hypothesis:

rows=0 loops=137415

I interpreted it as: “PostgreSQL tried this lookup 137,415 times and never found a row.”

That interpretation was too strong for two separate reasons.

First, EXPLAIN ANALYZE described one execution with one set of parameters and one snapshot of the data. Even a genuine zero for that execution would not prove that the relationship was empty for every date range, status, account, or historical record.

Second, and more subtly, the rows value of a node with multiple loops is not necessarily a total.

What rows=0 loops=137415 actually means

The PostgreSQL documentation explains that when a plan node runs more than once, loops is the number of executions while the displayed actual time and rows values are averages per execution.

That changes the meaning of the line completely.

Imagine a lookup that runs 100,000 times but finds only 100 rows in total. Its average is:

100 / 100000 = 0.001 rows per loop

In output that renders row averages as whole numbers, that can appear as:

rows=0 loops=100000

The node did not necessarily return zero rows overall. It returned too few rows per execution for the displayed average to make that visible.

The practical lesson is that this:

rows=0 loops=137415

does not safely translate to this:

total rows returned = 0

It tells us that the average number of rows returned per loop was displayed as zero. Before PostgreSQL 18, that average was rounded to a whole number, so a small but non-zero number of matches could hide behind it. PostgreSQL 18 changed EXPLAIN to output fractional row counts, which makes cases like this one more visible. PostgreSQL 18 release notes It does not remove the trap: a small enough average can still round to zero at the precision shown. Multiplying the displayed rows by loops does not help either, because the average was rounded before you multiply it. If a branch looks empty, the reliable check is to count the rows that actually go through that join path with a direct query.

This is exactly the kind of detail that becomes dangerous when the rare matches carry business meaning.

The business invariant caught what the plan could not

The optimized query passed the most obvious performance test: it completed faster.

But the report was not only expected to return quickly. It was expected to preserve financial totals. That gave me a simple invariant to compare:

SELECT SUM(amount)
FROM baseline_result;

SELECT SUM(amount)
FROM candidate_result;

The totals were different.

That comparison exposed the semantic regression immediately. The removed join path contained real values that were not present through the remaining path. They were rare enough to look irrelevant in the plan, but important enough to make the report incorrect.

This distinction matters because an execution plan answers questions such as:

• Which scans and joins did PostgreSQL execute?
• How many times did each node run?
• How close were estimated and actual row counts?
• Where did the query spend time or perform I/O?

It does not answer the domain question:

Does this rewritten query preserve every result the business considers correct?

That requires a different kind of test.

Performance equivalence is not semantic equivalence

Two queries can have very different plans and still return the same result. They can also look logically similar, run much faster, and differ only for a small set of records.

The second case is harder to detect because the optimization often removes exactly the complexity that handled those exceptional records.

Aggregate checks are a useful first defense:

WITH baseline AS (
  -- Existing query
),
candidate AS (
  -- Rewritten query
)
SELECT
  (SELECT COUNT(*) FROM baseline) AS baseline_count,
  (SELECT COUNT(*) FROM candidate) AS candidate_count,
  (SELECT SUM(amount) FROM baseline) AS baseline_total,
  (SELECT SUM(amount) FROM candidate) AS candidate_total;

But matching aggregates are not a complete proof either. Two result sets can have the same count and total while containing different rows.

For a stronger comparison, I can inspect differences in both directions:

WITH baseline AS (
  -- Existing query
),
candidate AS (
  -- Rewritten query
)
(
  SELECT * FROM baseline
  EXCEPT ALL
  SELECT * FROM candidate
)
UNION ALL
(
  SELECT * FROM candidate
  EXCEPT ALL
  SELECT * FROM baseline
);

If this returns rows, the two versions are not equivalent for that test case. EXCEPT ALL is useful here because it preserves duplicate counts instead of hiding multiplicity differences.

The comparison still needs representative parameters. Testing one date range or one account only proves equivalence for that slice of data.

The EXCEPT ALL comparison checks contents and multiplicity, not row order. For an ordered report, also compare the sequence under the complete ordering. Run both queries against the same snapshot with the same parameters; concurrent changes between separate measurements can produce differences unrelated to the rewrite. The CTEs above are templates: insert the actual queries before running them.

A safer workflow for query optimization

I now treat a SQL rewrite as both a performance change and a behavior change until evidence shows otherwise.

1. State the invariant before changing the query

The invariant should describe what must remain true, independently of the plan.

For a financial report, it might include:

• the same set of record identifiers;
• the same row multiplicity;
• the same total amount;
• the same totals grouped by status;
• the same ordering where ordering is part of the contract.

Without an explicit invariant, “the query still looks right” becomes the acceptance test.

2. Build a representative parameter matrix

I want cases that exercise more than the common path:

• narrow and wide date ranges;
• periods with and without historical records;
• accounts that use each relationship path;
• empty results;
• boundary dates and uncommon statuses.

This is especially important when the proposed optimization removes a fallback, an OR branch, a join, or a COALESCE. Those constructs often exist because the underlying data has more than one valid shape.

3. Compare results before comparing speed

For each parameter set, I first compare row-level differences and business aggregates. Only equivalent candidates move to performance evaluation.

This ordering prevents a fast result from creating pressure to rationalize a correctness difference later.

4. Then inspect EXPLAIN (ANALYZE, BUFFERS)

Once the candidate is semantically valid, the plan helps explain whether the rewrite actually reduced work.

I look at:

• estimated rows versus actual rows;
• loops, remembering that row and time values are averages per execution;
• buffers read versus cache hits;
• rows removed by filters;
• sorts or hashes spilling to disk;
• nodes that are cheap once but expensive when multiplied by thousands of loops.

The plan remains essential. It is simply answering a different question from the equivalence test.

5. Keep the baseline available

During the investigation, the old query is not dead code. It is the reference for behavior I intend to preserve; known defects still require independent domain assertions.

Keeping both versions executable makes it easier to test another date range when a suspicious difference appears. Deleting the baseline too early removes the fastest way to discover what the optimization changed.

There were warning signs earlier in the investigation

This was not the only moment when a locally reasonable change produced a surprising result.

Earlier, I replaced a date predicate that wrapped the filtered column in a function with a directly comparable range. The change fixed a large cardinality estimation error, but it also caused PostgreSQL to choose a different join strategy. Runtime went from roughly 3.4 seconds to 6.2 seconds for that test.

The rewrite was more index-friendly and the estimate was more accurate, yet the complete plan became slower.

I also corrected an ORDER BY that sorted formatted dates as DD/MM/YYYY strings. The old plan happened to reuse an existing sort more efficiently, while the correct ordering required additional work. The slower operation was still necessary because the old result order was wrong.

Both cases point to the same broader lesson: local plan improvements, lower costs, and familiar optimization rules are not the final objective. The final objective is a query that is correct for the domain and efficient for the workload.

Where the reasoning broke

Looking back, the mistake took two inference steps, and the plan supported neither of them.

EXPLAIN ANALYZE reported the execution using PostgreSQL's documented conventions: a lookup whose average row count, across 137,415 executions, was displayed as zero. I turned that into "this relationship never produces data". Then I turned that claim into a structural rewrite without first checking that both queries returned the same rows.

Each step felt small. Together they moved from a performance observation to a semantic conclusion, and nothing in between tested the semantics.

The rule I follow now is about order. Before a query rewrite gets compared on speed, it has to match the baseline on rows, multiplicity, totals and the edge-case parameters that exercise every relationship path. Then I read the plan to understand why the faster version is faster. If a branch looks empty in the plan, I take that as a reason to look for the rare data that goes through it, not as permission to delete the branch.

References

• PostgreSQL documentation: Using EXPLAIN
• PostgreSQL documentation: EXPLAIN