PostgreSQL Used the Index. It Still Processed Millions of Rows.

An explicit materialized CTE applied selective filters too late, turning a two-second reporting query into a 24-second problem.

A reporting query that normally completed in about two seconds started taking approximately 24 seconds.

The transaction table had grown from around 5 million to 55 million rows.

We expected to find a sequential scan or a missing index. Instead, PostgreSQL continued using the existing composite index.

Index Scan using idx_tx_tenant_created

That line gave us false confidence.

The index was working. PostgreSQL was simply using it to process far too many rows.

Disclosure: All table names, SQL, execution-plan fragments, row counts, timings, and buffer figures in this article are sanitized or fictionalized. They preserve the production failure pattern but do not reproduce a customer query, schema, execution plan, or measurement.

An Index Scan Is Not a Performance Verdict

An index scan answers one question:

How did PostgreSQL access the table?

It does not answer:

How much data did PostgreSQL process after choosing that access method?

An index can still:

• Read millions of index entries
• Visit millions of table tuples
• Apply filters after reading them
• Feed a large relation into a join
• Touch thousands of buffers
• Repeat work across many loops
• Produce an expensive sort or aggregate

The operator name is only the beginning of the investigation.

The developer should follow the rows through the complete plan.

The Query Looked Reasonable

The logical business query was simple:

SELECT t.id,
       d.item_code,
       d.amount
FROM transactions t
JOIN transaction_details d
  ON d.transaction_id = t.id
WHERE t.tenant_id = :tenantId
  AND t.created_at >= :start
  AND t.created_at < :end
  AND t.status = :status;

The transactions table already had this index:

CREATE INDEX idx_tx_tenant_created
ON transactions (tenant_id, created_at);

For an ordinary query with these predicates, PostgreSQL can normally use both tenant_id and created_at as an index condition.

That detail is important.

Simply wrapping the query in a CTE or moving the WHERE clauses around would not necessarily change anything. PostgreSQL can push predicates through many joins and subqueries automatically.

Our real query contained an additional structural detail.

The Real Query Had a Materialization Boundary

The reporting framework first created a reusable base relation. In sanitized form, the original query looked like this:

WITH report_base AS MATERIALIZED (
    SELECT t.id,
           t.created_at,
           t.status,
           d.item_code,
           d.amount
    FROM transactions t
    JOIN transaction_details d
      ON d.transaction_id = t.id
    WHERE t.tenant_id = :tenantId
)
SELECT id,
       item_code,
       amount
FROM report_base
WHERE created_at >= :start
  AND created_at < :end
  AND status = :status;

The framework had introduced report_base so several reporting branches could reuse the same structure.

The problem was MATERIALIZED.

PostgreSQL had to execute the CTE and store its result before the outer query could apply the date and status predicates.

The selective conditions existed in the SQL, but they were on the wrong side of an explicit optimization boundary.

PostgreSQL could not move them into the transaction scan because MATERIALIZED told it not to inline the CTE.

As a result, the index condition contained only tenant_id:

Index Cond: (tenant_id = $1)

The created_at range did not become part of the index condition because it was outside the materialized CTE.

That was the concrete reason selectivity arrived late.

We Initially Trusted the Access Path

We saw the expected index name and initially moved our attention elsewhere.

We checked:

• Spring Boot request latency
• Connection-pool pressure
• JVM CPU
• Network latency
• Report mapping
• Result serialization
• PostgreSQL I/O
• Lock activity

These were reasonable checks, but they did not explain why the query became slower as the table grew.

The SQL had not materially changed.

The index had not disappeared.

The data volume had changed.

The query shape had always performed unnecessary work. Five million rows were not enough to make the mistake operationally obvious.

At 55 million rows, the same shape became expensive.

Collect the Complete Execution Evidence

We returned to the query with:

EXPLAIN (
    ANALYZE,
    BUFFERS,
    VERBOSE,
    SETTINGS
)
WITH report_base AS MATERIALIZED (
    SELECT t.id,
           t.created_at,
           t.status,
           d.item_code,
           d.amount
    FROM transactions t
    JOIN transaction_details d
      ON d.transaction_id = t.id
    WHERE t.tenant_id = :tenantId
)
SELECT id,
       item_code,
       amount
FROM report_base
WHERE created_at >= :start
  AND created_at < :end
  AND status = :status;

ANALYZE executes the query, so use it carefully in production.

For modifying statements, EXPLAIN ANALYZE performs the write. Test those statements in a representative environment or use a carefully controlled transaction that is rolled back, while remembering that external side effects may not be reversible.

BUFFERS shows how much cached and physical page activity occurred. VERBOSE exposes more plan details, while SETTINGS shows non-default planner settings that may influence the result.

We did not stop at the first Index Scan.

We followed:

1. Rows emitted by the transaction scan
2. Rows entering the detail join
3. Rows produced by that join
4. Rows removed by the outer filter
5. Buffer activity
6. Repeated work represented by loops

The Before Plan Exposed the Real Work

A simplified plan excerpt looked like this:

CTE Scan on report_base
  (cost=... rows=92,000 ...)
  (actual time=... rows=180,000 loops=1)
  Filter:
    created_at >= $2
    AND created_at < $3
    AND status = $4
  Rows Removed by Filter: 8,520,000
  Buffers: shared hit=184,000 read=61,000

  CTE report_base
    -> Nested Loop
       (cost=... rows=900,000 ...)
       (actual time=... rows=8,700,000 loops=1)

       -> Index Scan using idx_tx_tenant_created
          on transactions t
          (cost=... rows=870,000 ...)
          (actual time=... rows=8,700,000 loops=1)
          Index Cond: (tenant_id = $1)

       -> Index Scan using idx_detail_transaction
          on transaction_details d
          (cost=... rows=1 ...)
          (actual time=... rows=1 loops=8,700,000)
          Index Cond: (transaction_id = t.id)

Planning Time: 4.8 ms
Execution Time: 24,000 ms

This plan makes the problem visible.

The transaction index scan emitted 8.7 million rows because it could use only tenant_id.

Those rows entered the nested-loop join. PostgreSQL executed the detail index scan 8.7 million times.

The materialized CTE produced 8.7 million joined rows. The outer filter then removed 8.52 million of them and returned approximately 180,000.

The expensive work had already happened.

Read Actual Rows Carefully

PostgreSQL’s row counters are easy to misread.

Consider this node:

actual rows=180,000 loops=1
Rows Removed by Filter: 8,520,000

actual rows=180,000 means the node emitted approximately 180,000 rows.

The 8.52 million removed rows are additional.

The node therefore inspected approximately:

180,000 emitted
+ 8,520,000 removed
= 8,700,000 rows considered

This would mean something very different:

actual rows=8,700,000
Rows Removed by Filter: 8,520,000

That node would have emitted 8.7 million rows and rejected another 8.52 million. It would have inspected more than 17 million rows.

Rows Removed by Filter is not included in the emitted actual rows value.

That distinction must be correct before drawing conclusions from a plan.

Always Interpret Rows With Loops

The actual rows value is normally reported per loop.

For example:

actual rows=1 loops=8,700,000

This does not mean the node returned only one row in total.

It means the node returned approximately one row per execution and ran 8.7 million times.

The rough total is:

actual rows x loops

In this scenario:

1 row per loop x 8,700,000 loops
    approximately equals 8,700,000 rows

Values are averaged and rounded, so multiplication is an estimate. Still, it reveals the scale of repeated work.

A small inner operation can become expensive when a nested loop executes it millions of times.

Follow Row Flow, Not Operator Names

The relevant row flow was:

The table exposes two problems.

First, selectivity arrived after the detail join.

Second, PostgreSQL underestimated the number of tenant rows by roughly ten times.

Bad cardinality estimates can lead the planner to choose a join strategy that looks cheap for the estimated input but performs poorly on the actual input.

The nested loop was not inherently wrong. It became expensive because its inner operation ran millions of times.

Check Statistics Before Rewriting SQL

Estimate errors can come from stale or insufficient statistics.

We checked when the tables were last analyzed:

SELECT schemaname,
       relname,
       n_live_tup,
       n_dead_tup,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN (
    'transactions',
    'transaction_details'
);

We also refreshed statistics in the test environment:

ANALYZE transactions;
ANALYZE transaction_details;

For skewed columns, the default statistics target may not describe the distribution well enough.

For example, one tenant may own a large percentage of the table while most tenants own very little.

The developer can inspect statistics:

SELECT attname,
       n_distinct,
       most_common_vals,
       most_common_freqs,
       histogram_bounds,
       correlation
FROM pg_stats
WHERE tablename = 'transactions'
  AND attname IN (
      'tenant_id',
      'status',
      'created_at'
  );

If columns are correlated, extended statistics may help:

CREATE STATISTICS stats_tx_tenant_status
    (dependencies, mcv)
ON tenant_id, status
FROM transactions;

ANALYZE transactions;

Statistics did not remove our explicit materialization boundary, but they explained part of the estimate difference and improved planner evidence.

A SQL rewrite should not become a substitute for basic statistics maintenance.

Root Cause: Selectivity Arrived Too Late

The report needed transactions for:

• One tenant
• One date range
• One status

Only about 180,000 rows matched the complete condition.

But the materialized base query selected every transaction for that tenant and joined each one to transaction_details first.

Approximately 8.7 million rows reached the join.

Then 8.52 million were discarded.

The database was doing exactly what the SQL requested.

The query’s logical result was correct. Its physical work was badly placed.

Why the Query Once Looked Fast

At 5 million total rows, the selected tenant owned a much smaller history.

The same plan still read more data than necessary, but:

• More pages remained cached
• Fewer index entries existed
• The nested loop ran fewer times
• The materialized relation was smaller
• Temporary memory and I/O pressure were lower

Nothing had to change in the code for the regression to appear.

The table grew until the hidden cost became visible.

This is common in reporting systems. A query can be functionally correct and operationally unstable for years before data growth exposes its real scaling behavior.

The Rewrite Reduced Rows Before the Join

We moved the selective predicates into the relation that reads transactions.

WITH eligible_transactions AS (
    SELECT id
    FROM transactions
    WHERE tenant_id = :tenantId
      AND created_at >= :start
      AND created_at < :end
      AND status = :status
)
SELECT t.id,
       d.item_code,
       d.amount
FROM eligible_transactions t
JOIN transaction_details d
  ON d.transaction_id = t.id;

Now PostgreSQL can apply the tenant and date range while scanning the existing composite index.

The status remains a filter because it is not part of the index.

Only eligible transaction IDs reach the detail join.

The developer could express the same intent with a subquery:

SELECT t.id,
       d.item_code,
       d.amount
FROM (
    SELECT id
    FROM transactions
    WHERE tenant_id = :tenantId
      AND created_at >= :start
      AND created_at < :end
      AND status = :status
) t
JOIN transaction_details d
  ON d.transaction_id = t.id;

A direct join without the original materialization boundary could also produce the same plan.

The syntax did not create the performance improvement.

Removing late materialization and making the selective predicates available to the transaction scan created it.

A CTE Is Not Automatically Materialized

In modern PostgreSQL versions, a non-recursive, side-effect-free CTE referenced once can often be inlined.

This query:

WITH eligible_transactions AS (
    SELECT id
    FROM transactions
    WHERE tenant_id = :tenantId
)
SELECT *
FROM eligible_transactions;

may be planned similarly to a subquery.

Writing WITH does not guarantee that PostgreSQL will materialize the result.

PostgreSQL 12 and later allow explicit control with MATERIALIZED and NOT MATERIALIZED.

You can request materialization:

WITH eligible_transactions AS MATERIALIZED (
    SELECT id
    FROM transactions
    WHERE tenant_id = :tenantId
)
SELECT *
FROM eligible_transactions;

Or permit the CTE to be folded into the parent query:

WITH eligible_transactions AS NOT MATERIALIZED (
    SELECT id
    FROM transactions
    WHERE tenant_id = :tenantId
)
SELECT *
FROM eligible_transactions;

Neither option should be added automatically.

MATERIALIZED can avoid repeated expensive work, but it can also prevent predicate pushdown and create a large intermediate result.

NOT MATERIALIZED can enable better optimization, but it may duplicate work when the CTE is referenced multiple times.

The plan should decide, not a general rule about CTEs.

The After Plan Confirmed the Improvement

The rewritten query produced a plan similar to this:

Nested Loop
  (cost=... rows=165,000 ...)
  (actual time=... rows=180,000 loops=1)
  Buffers: shared hit=27,000 read=4,000

  -> Index Scan using idx_tx_tenant_created
     on transactions t
     (cost=... rows=160,000 ...)
     (actual time=... rows=180,000 loops=1)
     Index Cond:
       tenant_id = $1
       AND created_at >= $2
       AND created_at < $3
     Filter: status = $4
     Rows Removed by Filter: 34,000

  -> Index Scan using idx_detail_transaction
     on transaction_details d
     (cost=... rows=1 ...)
     (actual time=... rows=1 loops=180,000)
     Index Cond: transaction_id = t.id

Planning Time: 3.9 ms
Execution Time: 3,000 ms

The existing index remained in use.

The difference was that created_at became part of the Index Cond.

PostgreSQL examined approximately:

180,000 emitted
+ 34,000 removed by status filter
= 214,000 transaction rows

Only 180,000 rows reached the detail join.

The inner detail lookup ran 180,000 times instead of 8.7 million times.

Before and After

The complete comparison looked like this:

The runtime improvement was useful.

The row and buffer reductions were stronger evidence because they explained why the runtime improved.

Buffers Revealed the Work Behind the Time

A plan may report:

Buffers: shared hit=184000 read=61000

A shared hit means PostgreSQL found the page in shared buffers. It did not need to read that page from the operating system at that moment.

A shared read means PostgreSQL had to bring the page into shared buffers.

Buffer hits are cheaper than physical reads, but they are not free. PostgreSQL still needs CPU and memory bandwidth to process cached pages.

After the rewrite:

Buffers: shared hit=27000 read=4000

Both values dropped substantially.

That supported the hypothesis that the query was doing less work, not merely benefiting from a warmer cache.

One Fast Rerun Is Weak Evidence

The second execution of a query often runs faster because more pages are cached.

The developer should not compare one cold execution of the old query with one warm execution of the new query.

A better process is:

1. Run each version several times.
2. Record buffer hits and reads.
3. Test during comparable system load.
4. Avoid clearing production caches for an experiment.
5. Compare row flow even when cache state differs.
6. Test narrow and broad parameter ranges.

Runtime is affected by concurrency, storage state, checkpoints, autovacuum, CPU pressure, and other workloads.

Rows, loops, and buffers provide a more stable explanation.

Verify Result Equivalence

A faster query is still wrong if it changes the report.

We compared both result sets:

WITH original_result AS (
    -- Original query without ORDER BY
),
rewritten_result AS (
    -- Rewritten query without ORDER BY
)
SELECT *
FROM original_result

EXCEPT ALL

SELECT *
FROM rewritten_result;

Then we checked the reverse direction:

WITH original_result AS (
    -- Original query without ORDER BY
),
rewritten_result AS (
    -- Rewritten query without ORDER BY
)
SELECT *
FROM rewritten_result

EXCEPT ALL

SELECT *
FROM original_result;

Both queries should return zero rows.

EXCEPT ALL preserves duplicate-count differences. That matters when joins can return multiple detail rows for one transaction.

We also compared:

SELECT COUNT(*), SUM(amount)
FROM (...);

Counts and totals are useful checks, but they are not sufficient by themselves. Two different result sets can have the same count and total.

Test Representative Parameter Ranges

One good plan does not prove that every report will behave well.

We tested:

A query optimized for a one-day report may still be expensive for a one-year export.

The developer should decide whether broad reports need asynchronous execution, pre-aggregation, partitioning, or an explicit range limit.

Watch for Parameter-Sensitive Plans

Prepared statements can introduce another complication.

A plan that works for a small tenant may be poor for a tenant containing millions of rows. PostgreSQL may choose between custom and generic plans depending on execution behavior.

Useful investigation commands include:

EXPLAIN (ANALYZE, BUFFERS)
EXECUTE report_query(
    :tenantId,
    :start,
    :end,
    :status
);

For diagnosis, compare plan behavior under different settings in a controlled session:

SET plan_cache_mode = force_custom_plan;

And:

SET plan_cache_mode = force_generic_plan;

These settings are diagnostic tools, not automatic production fixes.

If custom and generic plans behave very differently, the developer should investigate tenant skew, parameter ranges, prepared-statement behavior, and application-driver configuration.

Should We Add status to the Index?

The rewritten plan still showed:

Filter: status = $4
Rows Removed by Filter: 34,000

It may be tempting to change the index to:

CREATE INDEX idx_tx_tenant_status_created
ON transactions (tenant_id, status, created_at);

That could help if status is selective and frequently used with the same tenant and date predicates.

But an additional or wider index has costs:

• More storage
• More write amplification
• Longer maintenance operations
• Additional vacuum and cache pressure
• More planner choices
• Possible overlap with existing indexes

In our sanitized example, rejecting 34,000 rows after a selective date scan was acceptable. The main problem was the 8.52 million rows filtered after the join.

We kept the existing index and measured the rewritten plan first.

A covering index was not necessary to solve the incident.

Keep the Reporting SQL Explicit in Spring Boot

A reporting query this specific is often clearer as explicit SQL rather than a heavily reused repository method.

For example:

@Repository
public class TransactionReportRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;

    public TransactionReportRepository(
            NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<TransactionReportRow> findReportRows(
            long tenantId,
            Instant start,
            Instant end,
            String status) {

        String sql = """
            WITH eligible_transactions AS (
                SELECT id
                FROM transactions
                WHERE tenant_id = :tenantId
                  AND created_at >= :start
                  AND created_at < :end
                  AND status = :status
            )
            SELECT t.id,
                   d.item_code,
                   d.amount
            FROM eligible_transactions t
            JOIN transaction_details d
              ON d.transaction_id = t.id
            """;

        MapSqlParameterSource parameters =
                new MapSqlParameterSource()
                        .addValue("tenantId", tenantId)
                        .addValue(
                                "start",
                                Timestamp.from(start)
                        )
                        .addValue(
                                "end",
                                Timestamp.from(end)
                        )
                        .addValue("status", status);

        return jdbcTemplate.query(
                sql,
                parameters,
                transactionReportRowMapper()
        );
    }
}

The specialized SQL introduces more use-case-specific code.

That trade-off is reasonable when it gives the report a predictable retrieval boundary and makes its execution cost visible.

The developer should still stream or page very large results. A faster query can overwhelm application memory if the service loads millions of returned rows into one List.

Make Critical Query Plans Observable

Store representative plans for critical reports.

A plan captured before and after a change creates stronger review evidence than “it seemed faster.”

Monitor:

• Report execution time
• Rows returned
• Temporary file usage
• Shared buffer reads
• Database CPU and I/O
• Query-plan changes
• Table growth
• Statistics freshness
• Result volume by tenant and date range

Use pg_stat_statements when available to identify changes in total and mean execution cost over time.

Be careful with production EXPLAIN ANALYZE. It executes the query and can place real load on the database.

Do not include sensitive parameter values or customer data in shared plans.

Lessons Learned

• Index Scan describes an access method, not the total amount of work.
• actual rows are emitted rows. Rows Removed by Filter represents additional inspected rows.
• Per-loop row counts must be interpreted together with loops.
• Selective predicates should reduce the relation before expensive joins when semantics allow.
• SQL text order does not control execution order, but explicit materialization can restrict planner transformations.
• A CTE is not automatically an optimization boundary in modern PostgreSQL.
• Runtime alone is weak evidence. Rows, loops, buffers, and result equivalence make the case stronger.

Conclusion

PostgreSQL never stopped using the index.

Our mistake was treating the words Index Scan as proof that the access path was efficient.

The existing index first returned millions of tenant rows. Those rows entered a detail join. Only afterward did the report’s date and status conditions remove most of them.

The rewrite did not succeed because it used a CTE.

It succeeded because it made selective conditions available before the expensive join, and the execution plan proved that PostgreSQL performed less work.

The next time a slow query shows an Index Scan, do not stop at the operator name.

Ask:

How many rows did the index return, where were they filtered, and how much work happened afterward?