PostgreSQL indexing: the queries killing your app

A missing index on a table with 10,000 rows is invisible. The same missing index on a table with 10 million rows will take your app down. The problem is that most PostgreSQL performance issues develop gradually — queries that took 20ms at launch take 800ms two years later, and nobody noticed the gradual degradation until users started complaining.

This guide covers how to find slow queries, what the query planner is actually doing, and which indexes to add first based on real production impact data.

Finding slow queries: pg_stat_statements

The first thing to do on any PostgreSQL database is enable pg_stat_statements. It tracks execution statistics for every query that runs — total time, calls, mean time — and it's the fastest way to find what's actually slow in production.

-- Enable extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your slowest queries
SELECT
  round(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Run this on your production database. The queries at the top are your biggest opportunities. Mean time above 100ms is a problem. Above 500ms is urgent.

EXPLAIN ANALYZE: reading the query plan

Once you have a slow query, EXPLAIN ANALYZE shows you exactly what PostgreSQL is doing to execute it. The key things to look for:

Which indexes to add first

Not all missing indexes have the same impact. Prioritize by this order:

1. Foreign keys without indexes — every unindexed FK causes a sequential scan on joins. PostgreSQL doesn't create these automatically.
2. WHERE clause columns on large tables — any column that appears in a WHERE clause on a table over 100,000 rows without an index
3. ORDER BY columns — if you sort by a column frequently and it's not indexed, PostgreSQL has to sort in memory
4. Composite indexes for multi-column filters — if you always filter by (user_id, created_at), a composite index beats two separate indexes

-- Find tables with missing FK indexes (PostgreSQL 14+)
SELECT
  c.conrelid::regclass AS table_name,
  a.attname AS column_name,
  c.confrelid::regclass AS references
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
  SELECT 1 FROM pg_index i
  WHERE i.indrelid = c.conrelid
  AND a.attnum = ANY(i.indkey)
);

Indexes that hurt: what to remove

Every index you add slows down writes. On tables with heavy INSERT/UPDATE workload, unused indexes can cost more than they save. Find them with:

-- Find indexes that are never used
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND NOT indisprimary
AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

BRIN vs GIN vs GiST: when B-tree isn't the answer

The default index type is B-tree, which handles equality and range queries well. But for specific use cases, other index types perform significantly better:

• BRIN — for large append-only tables sorted by time (like logs or events). 1000x smaller than B-tree, fast for range scans on sequential data
• GIN — for full-text search and JSONB column queries. Much faster than B-tree for @> and @@ operators
• GiST — for geometric data, IP ranges, and fuzzy text matching with pg_trgm