The Postgres indexes I reach for first

Indexes are the highest-leverage performance tool most applications never use properly. A missing index turns a millisecond lookup into a full table scan that gets slower every day. An unnecessary one slows every write and wastes disk. Knowing which to add is most of database performance work.

Here's how I think about them in PostgreSQL, and how I check that they're actually being used.

Start with EXPLAIN, not guesses

Before adding any index, I look at what the query is actually doing.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

The words to look for are Seq Scan. That means Postgres read the entire table to find your rows. On a small table that's fine. On a large one it's the problem. The fix is an index that lets it jump straight to the matching rows, which shows up as an Index Scan instead.

ANALYZE runs the query and reports real timings, so you see the actual cost, not an estimate. I never add an index without confirming the scan type changed afterward.

The default: B-tree on your lookup columns

The plain B-tree index covers the vast majority of cases: equality, ranges, sorting, all of it.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Any column you filter or join on regularly is a candidate. Foreign keys are the classic miss. Postgres indexes primary keys automatically, but it does not index the foreign key columns pointing at them, so a join or a cascading delete on an unindexed foreign key scans the whole child table. That one catches almost everyone.

Composite indexes and column order

When you filter on two columns together, one index over both beats two separate indexes.

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, created_at);

Order matters, and this is the part people get wrong. This index serves queries filtering on customer_id alone, or customer_id and created_at together. It does not efficiently serve a query filtering on created_at alone, because that's the second column. The rule is leftmost-first: put the column you always filter on at the front, and the one you sometimes add after it. Think of it like a phone book sorted by last name then first name, which is useless if all you know is the first name.

For multi-tenant apps, this is why tenant_id goes first in nearly every composite index, since every query filters on it. I covered that specific case in multi-tenant SaaS in ASP.NET Core.

Partial indexes for the row you actually query

If you only ever query a slice of a table, index only that slice.

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

A ticketing system might have millions of closed orders and a few hundred open ones, but only ever query the open ones. A partial index covers just those rows, so it's tiny, fits in memory, and stays fast no matter how much closed history piles up. This is one of my favourite Postgres features and it's underused.

Covering indexes to skip the table entirely

Normally Postgres uses the index to find rows, then reads the table for the other columns. If you INCLUDE those columns in the index, it can answer from the index alone.

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

Now SELECT total, status FROM orders WHERE customer_id = 42 never touches the table. In EXPLAIN this shows up as an Index Only Scan, the fastest read Postgres offers. Worth it for hot read paths, not worth the extra index size for everything.

The cost of over-indexing

Every index has to be updated on every insert, update, and delete of the underlying row. Ten indexes on a table means ten writes for every one row change. On a write-heavy table that adds up, and I've seen apps where inserts crawled because someone indexed every column just in case.

So indexes aren't free. Add them for queries you actually run, confirm with EXPLAIN that they're used, and periodically check for unused ones.

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Any index with zero scans is pure overhead. Drop it. It's costing you write performance and disk for nothing.

The loop

The whole process is a loop: find the slow query, run EXPLAIN ANALYZE, spot the sequential scan, add the narrowest index that fixes it, confirm the plan changed. Do that a few times on your slowest endpoints and most database performance problems disappear. It pairs directly with fixing N+1 queries in your ORM, since the fastest query is the one you index well and only run once.