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. Depending on the PostgreSQL version and output precision, a small but non-zero number of matches can be hidden behind that value.

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.

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 oracle for the behavior I am trying to preserve.

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.

The plan did not lie

It would be tempting to say that EXPLAIN ANALYZE misled me.

It did not. It reported the execution using PostgreSQL's documented conventions. I assigned a stronger meaning to the output than it could support.

The plan told me that a lookup had an average row count displayed as zero across many executions. I turned that into a claim that the relationship never produced data. Then I turned that claim into a structural rewrite without first proving result equivalence.

The fix is not to trust execution plans less. It is to use them for the questions they can answer and pair them with checks for the questions they cannot.

A query optimization is complete only when it reduces work without changing the meaning of the result.

That means measuring time and buffers, but also comparing rows, totals, edge cases, and the business invariants hidden behind the SQL.

A fast query that returns the wrong answer is not an optimization. It is a regression with a better benchmark.

References

• PostgreSQL documentation: Using EXPLAIN
• PostgreSQL documentation: EXPLAIN