PostgreSQL Index Tuning Guide — How to Reduce Slow Queries with EXPLAIN ANALYZE

Why index tuning is often misunderstood

When a query is slow, many teams immediately say, “let’s add another index.”

But in PostgreSQL, poorly chosen indexes can increase write cost, consume space, and still fail to help the real query pattern.

The practical approach is:

• inspect the actual execution plan
• understand which filter, join, or sort dominates cost

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN shows the planned execution path
• EXPLAIN ANALYZE shows what actually happened

In real tuning work, EXPLAIN ANALYZE is far more useful because real row counts and timing matter more than estimated cost alone.

What to inspect first

Start with these:

• is there a Seq Scan
• is there a big gap between estimated rows and actual rows
• is sorting expensive
• is a Nested Loop exploding in repetitions
• is Bitmap Heap Scan doing heavy work

Those signals usually point to the right tuning direction quickly.

Seq Scan is not always bad

A sequential scan may be optimal when a large portion of the table must be read anyway.

It becomes suspicious when:

• a highly selective filter still results in full scan
• a large table is scanned for a small LIMIT
• frequently repeated filter and sort patterns have no supporting index

The question is not “why is Seq Scan present” but “why did the planner choose it here.”

Composite index ordering matters

Consider:

SELECT *
FROM orders
WHERE tenant_id = 10
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

A good candidate is:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

That works because the index matches:

• the main filtering columns first
• then the sort order

Index order is tightly tied to query shape.

Partial indexes can be extremely effective

If traffic focuses on a subset of rows, a partial index may outperform a full-table index.

CREATE INDEX idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'PAID';

Benefits:

• smaller index size
• lower write overhead
• better optimization for the most common filter path

OFFSET pagination becomes expensive

This pattern degrades badly at scale:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Keyset pagination is often better:

SELECT *
FROM orders
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 50;

With the right index, it remains stable on large tables.

Sorting can be the real bottleneck

Sometimes filtering is cheap but sorting is expensive.

Common examples:

• latest activity feeds
• recent events by status
• user-specific recent actions

In such cases, an index that combines filtering and ordering can remove a large sort cost.

Covering indexes can help selectively

PostgreSQL supports INCLUDE:

CREATE INDEX idx_orders_lookup
ON orders (tenant_id, created_at DESC)
INCLUDE (status, total_amount);

This can reduce heap access for some lookup patterns.

But it should be used carefully because larger indexes also mean higher maintenance cost.

Statistics problems can break good plans

If estimated rows and actual rows differ heavily, planner statistics may be part of the issue.

Check:

• whether ANALYZE runs often enough
• whether data distribution is heavily skewed
• whether join columns are represented well in statistics

Without accurate statistics, even a good index can be ignored.

Common mistakes

• creating overlapping indexes
• optimizing reads while ignoring write amplification
• indexing low-cardinality columns blindly
• choosing composite index order without verifying query patterns
• tuning based on assumptions instead of the real ORM-generated SQL

A practical tuning flow

1. identify the slow query
2. capture EXPLAIN ANALYZE
3. isolate filter, join, and sort cost
4. design indexes around the most frequent query pattern
5. validate both read gain and write impact after deployment

Closing thoughts

PostgreSQL index tuning is not about creating more indexes. It is about reading the execution plan and removing the most expensive work.

In practice, the best gains usually come from a disciplined process:

• inspect the real query
• read the actual plan
• add fewer but more precise indexes

rather than blindly indexing every column that appears in SQL.