PostgreSQL Indexes That Actually Speed Up Your Queries (Most Don't)

80% of indexes I see in production databases are useless or actively harmful. They were added because a query was slow, the developer guessed at a fix, the query got faster (or didn't), and nobody validated. The index sat there forever, slowing down every write.

PostgreSQL indexes are not free. Every insert, update, and delete has to update every index. A table with 10 indexes writes 11 rows on every insert. That's the trade, read speed for write speed.

Here's how I actually do it.

The single most important tool: EXPLAIN ANALYZE

Not EXPLAIN. EXPLAIN ANALYZE. The difference matters.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '123';

Output:

Index Scan using orders_user_id_idx on orders  (cost=0.42..8.44 rows=1 width=...) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (user_id = '123'::text)
Planning Time: 0.123 ms
Execution Time: 0.035 ms

What to look for:

• Seq Scan, Postgres scanned the whole table. Bad for large tables, fine for small.
• Index Scan, Postgres used the index. Good.
• Index Only Scan, Postgres used the index and didn't touch the table. Best.
• actual time, real numbers, not estimates.
• rows, how many rows Postgres actually returned at each step.

EXPLAIN without ANALYZE shows you what Postgres thinks it will do. EXPLAIN ANALYZE shows you what it actually did. The estimates can be wrong, that's often the bug.

Pick the right index type

PostgreSQL has more than B-tree. Most teams never use the others.

B-tree (default)

Handles: =, <, >, BETWEEN, ORDER BY, IS NULL.

CREATE INDEX idx_orders_user_id ON orders(user_id);

Use for: anything with equality or range. The default for a reason.

GIN (Generalized Inverted Index)

Handles: full-text search, JSONB operators, array operators.

-- For JSONB columns
CREATE INDEX idx_products_metadata ON products USING gin(metadata);
 
-- Now this query uses the index:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- For full-text search
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', body));

Use for: any @>, ?, @?, @@ operator on JSONB, array containment, full-text search.

GiST (Generalized Search Tree)

Handles: geometric operations, overlap, nearest-neighbor.

CREATE INDEX idx_locations_coords ON locations USING gist(point);

Use for: PostGIS, range types, geometric queries.

BRIN (Block Range Index)

Handles: naturally-ordered tables (time-series data).

CREATE INDEX idx_events_time ON events USING brin(created_at);

Use for: huge tables where data is naturally ordered by time. BRIN indexes are tiny (KB instead of GB) and fast for range scans on ordered data.

Composite indexes and column order

This is the gotcha. In a multi-column index, column order matters.

-- Index A
CREATE INDEX idx_a ON orders(status, created_at);
 
-- Index B
CREATE INDEX idx_b ON orders(created_at, status);

These are not the same.

• WHERE status = 'shipped' AND created_at > '2026-01-01' → uses Index A
• WHERE status = 'shipped' → uses Index A
• WHERE created_at > '2026-01-01' → uses Index B (not A, Index A can't be used without the leading column)
• WHERE created_at > '2026-01-01' AND status = 'shipped' → uses Index B (or A, depending on selectivity)

The rule: put equality columns first, range columns last. And put the most selective column first if you're not sure.

Index-only scans and covering indexes

If your query only needs columns covered by the index, Postgres can skip the table read entirely.

-- Query
SELECT user_id FROM orders WHERE status = 'shipped';
 
-- Index covers user_id and status → Index Only Scan
CREATE INDEX idx_orders_covering ON orders(status, user_id);

This is huge for hot queries. The index has everything; the table is never touched.

For queries that need more columns, use INCLUDE:

CREATE INDEX idx_orders_status ON orders(status) INCLUDE (user_id, total);

The INCLUDE columns are stored in the index but don't affect sort order. You get index-only scans without bloating the B-tree structure.

When indexes hurt

Every index has a cost:

-- Insert one row into a table with 5 indexes
INSERT INTO orders(...);
-- Postgres writes:
-- 1. The heap row
-- 2-6. Five index entries

For a write-heavy table, this adds up fast. I've seen tables with 20 indexes where inserts took 50ms each. Dropping unused indexes brought it back to 2ms.

The maintenance pattern:

-- Find unused indexes
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 means the index has never been used since the last stats reset. Drop it.

But: some indexes exist for uniqueness constraints. Don't drop those, they enforce data integrity even if queries don't use them.

The duplicate index problem

-- Someone created these over time:
CREATE INDEX idx_a ON orders(user_id);
CREATE INDEX idx_b ON orders(user_id, status);  -- supersedes idx_a

idx_a is now dead weight. idx_b handles queries on user_id alone too. Drop idx_a.

Find duplicates:

SELECT
  array_agg(indexrelname) AS indexes,
  indkey
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
GROUP BY indkey, relname
HAVING count(*) > 1;

The workflow I use

1. Profile the slow query with EXPLAIN ANALYZE.
2. Identify the scan type, Seq Scan on a large table is the signal.
3. Check if an existing index could work, maybe you just need to rewrite the query.
4. Add an index matching the query, equality first, range last.
5. Re-run EXPLAIN ANALYZE, verify the index is used.
6. Measure write impact, EXPLAIN ANALYZE INSERT INTO ... before and after.
7. After 3 months, check pg_stat_user_indexes, drop the unused ones.

Step 5 is the one most teams skip. They add an index, assume it helped, and never check. Half the time, Postgres ignores the index because the query is written wrong or the table is too small.

An unused index is a tax on every write, for zero read benefit. Add them deliberately, measure them, and drop the ones that don't earn their keep.

Want help with database performance?

I optimize PostgreSQL databases for clients, proper indexes, real EXPLAIN ANALYZE readings, write impact measured. Let's talk.