PostgreSQL Was Not Slow. Our Index Was Useless.

Performance Engineering | By |

A practical backend lesson on slow reporting APIs, composite indexes, and why “there is an index” does not mean PostgreSQL has the right path.

A database index helps PostgreSQL find rows faster.

In a simple case, an index works like a shortcut. Instead of scanning the whole table, PostgreSQL can use the index to reach matching rows more directly.

That is the idea.

But the important detail is this:

An index only helps when it matches how the query actually searches, filters, sorts, and limits data.

In this scenario, PostgreSQL was not slow.

The reporting API was slow because the existing index did not match the real WHERE and ORDER BY pattern.

The Real Problem

Near month-end, a reporting API started getting slow.

The same API worked fine earlier in the month. It also worked fine when the table had less data.

From the outside, PostgreSQL looked healthy.

CPU was not obviously failing.

Memory looked acceptable.

Connections were stable.

There was no dramatic database outage.

So the first guesses were normal:

Maybe month-end traffic was too high.

Maybe PostgreSQL needed tuning.

Maybe the server needed more CPU or RAM.

Maybe the query needed an index.

Then came the misleading sentence:

“The table already has an index.”

That sounded reassuring.

But it was not enough.

The Query That Became Slow

The reporting API filtered records by:

  • product_id
  • status

Then sorted by:

  • created_date DESC

The query looked like this:

SELECT *
FROM report_transaction
WHERE product_id = :productId
  AND status = :status
ORDER BY created_date DESC
LIMIT 100;

This query asks PostgreSQL a very specific question.

It does not ask for all records.

It asks:

“Give me the latest 100 records for this product and this status.”

That access pattern matters.

The filters and sort order should guide the index design.

The Misleading Comfort Of “There Is An Index”

The table already had this index:

CREATE INDEX idx_report_transaction_customer_id
ON report_transaction (customer_id);

This index may be useful for another feature.

For example, it can help a query that searches by customer_id.

But it does not help much for the reporting query above.

The slow query was not mainly filtering by customer_id.

It was filtering by product_id and status, then sorting by created_date.

So the table technically had an index.

But for this query, PostgreSQL did not have the right path.

That is the trap.

Having an index is not the same as having a useful index.

Why The Existing Index Did Not Help

The existing index answered this kind of question:

Find records for this customer.

But the reporting API asked this question:

Find records for this product and status, newest first.

Those are different questions.

PostgreSQL still had to inspect too many rows.

It still had to filter out rows that did not match.

It still had to sort records by created_date DESC.

When the table was small, this was not obvious.

PostgreSQL could scan, filter, sort, and still return fast enough.

Near month-end, more data made the weak access pattern visible.

Start With Slow Query Logs

The developer should not start database tuning from guesses.

Start with the query that actually hurts.

Slow query logs help identify:

  • Which SQL is slow
  • How often it runs
  • How long it takes
  • Whether the same query repeats many times
  • Which endpoint or workflow triggers it

In PostgreSQL, slow query logging can be enabled with a setting like this:

log_min_duration_statement = 500

This logs queries that take longer than 500 milliseconds.

The exact threshold depends on the system. For some APIs, 500 ms is already too slow. For large exports, several seconds may be acceptable.

The point is to capture real evidence before changing indexes.

Use EXPLAIN ANALYZE

After finding the slow SQL, use EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT *
FROM report_transaction
WHERE product_id = 101
  AND status = 'SUCCESS'
ORDER BY created_date DESC
LIMIT 100;

EXPLAIN shows the plan PostgreSQL expects to use.

EXPLAIN ANALYZE actually runs the query and shows what happened.

The developer should look for:

  • Sequential scans
  • Large row filtering
  • Expensive sort steps
  • Bad row estimates
  • Index scans that still read too much data
  • High execution time
  • Many rows removed by filter

A simplified bad plan may look like this:

Limit
  -> Sort
       Sort Key: created_date DESC
       -> Seq Scan on report_transaction
            Filter: ((product_id = 101) AND (status = 'SUCCESS'))
            Rows Removed by Filter: 850000
Execution Time: 2200 ms

This tells a clear story.

PostgreSQL scanned a lot of rows, filtered many of them, sorted the result, then returned 100 rows.

That is expensive when the table grows.

Design The Index From The Query Pattern

Index design should start from the query.

The query has equality filters:

WHERE product_id = ?
  AND status = ?

Then it has a sort:

ORDER BY created_date DESC

So the composite index should match that pattern:

CREATE INDEX idx_report_transaction_product_status_created_date
ON report_transaction (product_id, status, created_date DESC);

This index gives PostgreSQL a better path.

It can first narrow by product_id.

Then narrow by status.

Then read matching rows in newest-first order.

That means less scanning and less sorting.

Why This Composite Index Helps

With the new index, PostgreSQL can answer the query closer to the way the API asks it.

The query wants:

product_id = 101
status = SUCCESS
newest rows first
only 100 rows

The index is arranged like this:

product_id -> status -> created_date DESC

That is a much better match.

A simplified improved plan may look like this:

Limit
  -> Index Scan using idx_report_transaction_product_status_created_date
       Index Cond: ((product_id = 101) AND (status = 'SUCCESS'))
Execution Time: 25 ms

This is the kind of shape the developer wants to see.

The exact output depends on table size, data distribution, PostgreSQL version, statistics, and selected columns.

But the important change is that PostgreSQL can use the index to reach the right rows in the right order.

Spring Boot Repository Example

A Spring Boot repository method may look like this:

@Query("""
    SELECT t
    FROM ReportTransaction t
    WHERE t.productId = :productId
      AND t.status = :status
    ORDER BY t.createdDate DESC
""")
List<ReportTransaction> findLatestByProductAndStatus(
    Long productId,
    String status,
    Pageable pageable
);

This repository method matches the SQL pattern.

The Pageable can provide LIMIT behavior depending on how it is called.

The developer should still verify the generated SQL and query plan.

ORM code can hide SQL details, but PostgreSQL only executes SQL, not intentions.

Be Careful With SELECT *

The example query uses SELECT * because it is common in application code.

But in reporting APIs, selecting every column can make a query heavier than necessary.

If the endpoint only needs a few fields, select only those fields.

A smaller result can reduce I/O, memory use, network transfer, and object mapping cost.

The index still needs to match the query pattern, but returning fewer columns can help the whole request path.

Add Pagination Carefully

Many reporting APIs use pagination.

For example:

This is fine for early pages.

But large OFFSET values can still become slow.

LIMIT 100 OFFSET 500000;

PostgreSQL may still need to walk through many rows before returning the next 100.

For large result sets, keyset pagination is often better.

Example:

SELECT *
FROM report_transaction
WHERE product_id = :productId
  AND status = :status
  AND created_date < :lastSeenCreatedDate
ORDER BY created_date DESC
LIMIT 100;

Instead of saying “skip 500,000 rows,” keyset pagination says “continue after the last row I saw.”

This usually works better for deep pagination.

If multiple rows can have the same created_date, add a tie-breaker such as id:

ORDER BY created_date DESC, id DESC

Then include the same columns in the keyset condition and index design.

Safer Production Index Creation

Creating an index on a large production table should be done carefully.

A normal CREATE INDEX can block writes.

For PostgreSQL production systems, consider:

CREATE INDEX CONCURRENTLY idx_report_transaction_product_status_created_date
ON report_transaction (product_id, status, created_date DESC);

CREATE INDEX CONCURRENTLY reduces locking impact.

But it has trade-offs.

It takes longer.

It cannot run inside a normal transaction block.

It can still consume CPU, disk, and I/O.

The developer should test in staging first and monitor production while it runs.

Check The Index After Creation

After creating the index, run EXPLAIN ANALYZE again.

The goal is not just to create the index.

The goal is to confirm PostgreSQL uses it.

If PostgreSQL does not use the new index, possible reasons include:

  • Table statistics are outdated
  • The query returns too many rows
  • Data distribution makes the index less useful
  • The query condition differs from the index design
  • PostgreSQL estimates a sequential scan is cheaper
  • The selected columns require many table lookups

The developer can run:

ANALYZE report_transaction;

This updates PostgreSQL statistics for the table.

Do this carefully on large production systems and follow your team’s database operation process.

Indexes Are Not Free

Indexes improve some reads.

But they add cost.

Every new index needs storage.

Every insert must update the index.

Some updates must update the index.

More indexes mean more maintenance work for PostgreSQL.

So the developer should not add indexes blindly.

A new index makes sense when:

  • The query is important
  • The query is slow
  • The access pattern is stable
  • The table is large enough to benefit
  • The read improvement is worth the write cost

In this scenario, the reporting API was read-heavy and users were affected near month-end.

The composite index was justified.

But it was still a trade-off, not a free fix.

A Practical Debugging Flow

The developer can use this flow for slow reporting APIs:

  1. Find the slow query from logs.
  2. Run EXPLAIN ANALYZE.
  3. Identify the WHERE filters.
  4. Identify the ORDER BY.
  5. Check pagination.
  6. Compare the existing indexes with the query pattern.
  7. Design a composite index based on the real access path.
  8. Test in staging with production-like data.
  9. Create the index safely in production.
  10. Run EXPLAIN ANALYZE again.
  11. Monitor response time, write cost, and index size.

This process is much better than guessing from dashboards alone.

Dashboards show symptoms.

Query plans show the path.

Checklist For Slow Reporting Queries

When a reporting query becomes slow, ask:

  • Which query is actually slow?
  • What are the WHERE filters?
  • What is the ORDER BY?
  • Is there pagination?
  • Does the existing index match this exact pattern?
  • How many rows are scanned?
  • Is PostgreSQL sorting a large result set?
  • Is the query read-heavy enough to justify a new index?
  • What is the write cost of the new index?
  • Does month-end data volume change the query behavior?

This checklist helps keep the discussion practical.

It also prevents the team from blaming PostgreSQL too early.

Expected Result

After adding the right composite index:

  • The reporting API responds faster near month-end.
  • PostgreSQL scans fewer rows.
  • Sorting work is reduced or avoided.
  • The query plan matches the actual access pattern.
  • Hardware changes are not needed for this specific issue.
  • The team can explain why the new index helps.
  • Other reporting queries can be reviewed using the same method.

The biggest win is not only speed.

The bigger win is understanding why the query became faster.

Important Notes

Do not add indexes blindly.

An index on the wrong column may not help the slow query.

Always compare indexes with WHERE and ORDER BY.

Use EXPLAIN ANALYZE, not guesses.

Indexes increase storage and write cost.

Use CREATE INDEX CONCURRENTLY carefully in production.

Month-end data volume can expose weak query patterns.

Large OFFSET pagination may still be slow even with a good index.

Also remember that development data can be misleading. A query that is fast on 10,000 rows may behave very differently on 100 million rows.

Recommendation

Start index design from the query, not from the column that looks important.

A column like customer_id may be important to the business.

But if the slow query filters by product_id and status, then sorts by created_date, an index on customer_id will not save it.

For reporting APIs, review indexes when access patterns change.

Dashboards, exports, reconciliation jobs, and month-end workflows often grow over time.

The index strategy that worked last year may not match the queries that matter now.

Conclusion

PostgreSQL was not the problem.

The query did not have a useful path through the data.

The table had an index, but it answered the wrong question.

In this case, the fix was to stop asking whether an index existed and start asking whether the index matched the query. The useful index followed the real access pattern: product_id, status, then created_date DESC.

That is why “there is an index” should never end the investigation.

The better question is:

“Does this index match how the query really accesses the data?”

Performance tuning should start with the real query plan, not guesses.

Sometimes the best database fix is not more CPU, more RAM, or a bigger server.

Sometimes it is giving PostgreSQL the right path.

Related Posts