PostgreSQL Performance Tuning: Indexing Strategies That Matter

2 min readAdvanced
PostgreSQLPerformanceSQLDatabase

Before you add another index, you need to know what the database actually does with your query. PostgreSQL ships with an excellent teacher: EXPLAIN (ANALYZE, BUFFERS).

Read the plan, not the table

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT *
FROM orders
WHERE customer_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

The numbers that matter are actual time, rows, and buffers. If you see Seq Scan on a table that should be a few hundred thousand rows wide, that is your smoking gun. If you see a Bitmap Heap Scan with a high Buffers: shared hit, the row estimate is probably off and ANALYZE is overdue.

Column order in a composite index

For a composite index, PostgreSQL can only use range conditions (>, <, LIKE 'x%') on the trailing columns. The classic rule:

  1. Leading columns: equality conditions.
  2. Trailing columns: range / sort conditions.

So for the query above:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

This one index serves the WHERE filter, the ORDER BY, and the LIMIT — the index scan stops after 20 rows instead of fetching and sorting the whole result.

Partial indexes for hot subsets

When only a slice of the table is queried constantly — pending orders, unread notifications — a partial index is dramatically smaller and faster to probe:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status IN ('NEW', 'CONFIRMED');

Smaller index, less WAL traffic, hotter cache. Every tuple you can exclude from an index is a win you never have to tune later.

Covering indexes to skip the heap

If a query only needs a handful of columns, an index-only scan avoids fetching the row from the table at all:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total);

Use INCLUDE for columns used in SELECT, WHERE, or ORDER BY — not for columns used as scan keys.

When to stop adding indexes

Every index taxes every write. A table that receives 1,000 inserts/s will feel every redundant index. Before adding one, ask:

  • Is this a real query, or a hypothetical one? Profile first.
  • Does an existing index already cover the leading column?
  • Can the query be rewritten to reuse an existing index?
  • Could a partial index give 90% of the benefit?

Run pg_stat_user_indexes to find indexes that never get used and drop them. An unused index is pure cost.

The takeaway

Indexing is a conversation with the query planner. EXPLAIN first, composite indexes equality-first, partial and covering indexes for hot paths, and ruthless removal of what is not used.

← Back to technical articles