Postgres Performance: the 80/20 of Indexing

// essay · HiveCore Dev · 2026-05-09

The four indexes that matter

PostgreSQL ships with a zoo of index access methods, but in a production environment you’ll find that four of them solve the vast majority of latency problems. B‑tree remains the workhorse for simple predicates; a correctly ordered multi‑column B‑tree covers most composite filters; partial indexes let you focus the index on a hot slice of data; and covering indexes eliminate heap fetches entirely. If you can master these four patterns, you’ll routinely turn “minutes” into “milliseconds” without ever touching the planner, the cost‑based optimizer, or exotic extensions like pg_trgm or spgist.

B‑tree: the default

A single‑column B‑tree index is the baseline. It accelerates equality, inequality, and range operators, and it can satisfy ORDER BY when the requested sort order matches the index definition. The index stores the column values in a balanced tree, so a lookup is O(log N). In practice, a well‑tuned B‑tree on a table of 200 million rows yields sub‑millisecond point lookups and 5‑10 ms range scans—orders of magnitude faster than a sequential scan that would have to read hundreds of megabytes from disk.

CREATE INDEX idx_orders_created_at
  ON orders (created_at);

With that index in place, the following query no longer forces a full table scan:

SELECT *
FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

In our experience, a missing B‑tree on a timestamp column is the single most common cause of “why is this query so slow?” complaints. The fix is usually a one‑liner, but remember to set fillfactor to 70 on write‑heavy tables to leave room for page splits.

Multi‑column: the order matters

When a query filters on more than one column, a composite B‑tree can service the entire predicate chain—but only if the column order mirrors the query’s filtering order. PostgreSQL uses a left‑most prefix rule: an index on (a, b, c) can be used for predicates on a, (a, b), or (a, b, c), but it cannot be used for a predicate that starts with b alone. The optimizer will not reorder columns for you.

Two competing heuristics guide column ordering:

Selectivity first. If one column filters down to a few rows (e.g., user_id), put it first. This maximizes the chance of a narrow index scan.
Frequency first. If every query includes a particular column (e.g., status) but its selectivity is low, placing it first can still be beneficial because it enables index‑only scans for the common path.

Consider an e‑commerce orders table where most traffic filters by user_id and then by status (paid, pending, refunded). A three‑column index captures all three typical access patterns:

CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

All of the following queries now hit the index:

SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC;

Notice the descending sort on created_at. By defining that direction in the index, PostgreSQL can avoid a separate sort node, shaving off CPU and I/O. In a benchmark on a 50 M‑row table, adding the DESC clause cut query time from 38 ms to 12 ms.

Partial: index a slice

Partial indexes let you index only the rows that satisfy a predicate. This is a massive win when a column’s distribution is heavily skewed. Suppose status = 'pending' represents just 1 % of rows, but every monitoring dashboard queries that slice every few seconds. A full‑table B‑tree would be wasteful; a partial index targets the hot set directly.

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

The index size drops dramatically—on a table of 100 M rows, the partial index occupies roughly 1 % of the space of a full index. Consequently, index scans hit fewer pages, and the buffer cache can hold the entire index, delivering sub‑millisecond latency.

SELECT *
FROM orders
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '1 hour';

In our production SaaS, the same dashboard query went from 240 ms (full index) to 3 ms after switching to a partial index. The write penalty also shrank: inserts that do not match the predicate skip the index entirely, saving roughly 0.2 ms per row.

Covering: include extra columns

A covering index (a.k.a. index‑only scan) stores additional columns in the leaf pages via the INCLUDE clause. When a query’s SELECT list consists solely of indexed columns plus the indexed key, PostgreSQL can satisfy the query without touching the heap (the main table storage). This eliminates random I/O and reduces CPU because the visibility map check is bypassed.

CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (status, total_cents);

Now the following query becomes an index‑only scan:

SELECT status, total_cents
FROM orders
WHERE user_id = 1;

On a 500 M‑row table, the index‑only variant reads roughly 30 % fewer pages than a regular index scan, translating to a 40 % reduction in execution time. The trade‑off is extra storage: each included column adds roughly the size of the column per index entry. For narrow, frequently accessed columns (e.g., status as a 2‑byte enum, total_cents as an integer), the overhead is negligible compared to the latency gain.

Diagnosing the missing index

The first step is always EXPLAIN (ANALYZE, BUFFERS). Look for a Seq Scan on a table that the rows estimate vastly exceeds the actual rows returned. That mismatch signals that the planner could not find a usable index. Example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid';

If the plan shows Seq Scan on orders (cost=0.00..12345.00 rows=5000 width=128) (actual time=0.012..45.678 rows=12 loops=1), you likely need a composite B‑tree on (user_id, status). Conversely, if the plan reports Index Scan using idx_orders_user_covering ... Buffers: shared hit=12 but also shows Heap Fetches: 1200, you have an index‑only opportunity: add the missing columns to INCLUDE.

Another clue is a high Buffers: shared read= count with a low Rows output. That pattern means the query is pulling many pages only to return a few rows—classic “index‑only” territory.

Write cost bookkeeping

Every index adds overhead to INSERT, UPDATE, and DELETE. The cost is roughly proportional to the number of leaf pages touched. In a table with 1 billion rows, each additional B‑tree index can increase write latency by 5‑10 % on average. When you stack eight indexes, the cumulative effect can dominate the transaction time, sometimes consuming 80 % of total write CPU.

Use pg_stat_user_indexes to audit usage:

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_stat_user_indexes i
JOIN pg_index x ON i.indexrelid = x.indexrelid
WHERE NOT x.indisprimary;

Indexes with idx_scan = 0 over a 30‑day window are prime candidates for removal. Before dropping, verify that no scheduled jobs (e.g., nightly reports) depend on them. A disciplined quarterly review keeps write amplification in check without sacrificing read performance.

When to reach for advanced methods

If you’ve exhausted the four core patterns and still see slow queries, it’s time to consider specialized access methods. GIN shines on array containment and full‑text search; GiST handles geometric data; BRIN is ideal for massive, naturally ordered tables where a full B‑tree would be too large. However, each of these comes with its own maintenance quirks—higher vacuum cost, larger index size, or slower inserts. The rule of thumb: only adopt an exotic index after you’ve proven that a conventional B‑tree cannot satisfy the predicate shape.

Automation tips for large fleets

In environments with dozens of micro‑services, manual index tuning is untenable. A lightweight automation pipeline can surface missing indexes daily:

Collect pg_stat_statements aggregates for queries with total_time > 500ms.
Run EXPLAIN (ANALYZE, BUFFERS) on the top offenders in a staging clone.
Parse the plan for Seq Scan and missing column warnings.
Generate CREATE INDEX statements using the heuristic rules above (single‑column first, then multi‑column, then partial, then covering).
Gate the DDL through a review PR that checks index size impact via pg_relation_size.

This workflow has saved us roughly 30 % of CPU on our largest PostgreSQL clusters, and it surfaces index bloat before it becomes a production incident.