Database · Nov 18, 2025 · 6 min read

PostgreSQL Indexing: A Practical Guide for Faster Queries

An index turns a full-table scan into a targeted lookup — but the wrong index can slow down writes and still never be used.

Indexing is the highest-leverage database optimisation you can make. The trick isn't adding many indexes; it's adding the right ones for the queries you actually run.

Start with EXPLAIN ANALYZE

Never guess. Prefix a slow query and read the plan. A sequential scan over a large table is the signal you're looking for:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Seq Scan on orders  (cost=... rows=...)
--   Filter: (customer_id = 42)

If Postgres is scanning the whole table, an index is the fix. If it's already using one, tune elsewhere.

B-tree indexes and column order

The default and most common index type is B-tree. For composite indexes, order matters most: put equality-filtered columns first, then range/sort columns.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

This single index serves both the WHERE customer_id = ? filter and the ORDER BY created_at sort. Because of the leftmost-prefix rule, it also helps queries filtering on customer_id alone — but not on created_at alone.

Covering and partial indexes

A covering index includes extra columns so the query is answered from the index without touching the table:

CREATE INDEX idx_orders_covering
ON orders (customer_id) INCLUDE (status, total);

A partial index only indexes the rows you care about, which keeps it small and fast:

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

Common mistakes

• Indexing low-cardinality columns (like a boolean) on their own — the planner will often ignore them.
• Creating an index per query instead of one composite index that covers several.
• Forgetting that every index adds write cost on insert and update.
• Adding indexes without checking EXPLAIN afterwards.

Measure, then move on

Add one index, re-run EXPLAIN ANALYZE, confirm the plan changed, and check the timing improvement. If a query is still slow after one well-designed index, the problem is usually the query shape or the data model — not a missing index.