A query that runs in 2 milliseconds with ten thousand rows can take 45 seconds with ten million rows if the index situation is wrong. Database performance issues are almost always index issues. Here is how to diagnose and fix them.
Understanding Sequential Scans vs Index Scans
When PostgreSQL executes a query, it chooses an execution plan. The two most common approaches are:
- Sequential Scan (Seq Scan): Read every row in the table, filter by the WHERE clause. Fine for small tables or when you need most of the data. Terrible for large tables with selective filters.
- Index Scan: Use an index to jump directly to matching rows. Fast for selective queries on large tables.
The way to see which one PostgreSQL is choosing is EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT * FROM shares WHERE slug = 'my-custom-slug';If the output shows Seq Scan on a table with millions of rows for a selective query, you need an index.
Creating the Right Index
For exact-match lookups on a single column:
CREATE INDEX idx_shares_slug ON shares(slug);For queries filtering on multiple columns together:
-- If you frequently query: WHERE user_id = $1 AND is_permanent = TRUE
CREATE INDEX idx_shares_user_permanent ON shares(user_id, is_permanent);Column order matters in composite indexes. The most selective column (the one with more unique values) should usually come first.
Partial Indexes: Often Overlooked
A partial index only covers rows matching a condition. If you mostly query a small subset of your data, this saves significant space and speeds up those specific queries:
-- Index only permanent, public shares for the sitemap query
CREATE INDEX idx_permanent_public ON shares(slug, updated_at)
WHERE is_permanent = TRUE AND password_hash IS NULL;When Not to Index
Indexes are not free. Every index slows down INSERT, UPDATE, and DELETE operations because the index must be maintained. They also consume disk space and memory.
Do not index columns that:
- Have very low cardinality (boolean columns indexing often hurts more than it helps)
- Are rarely used in WHERE clauses or JOINs
- Are on small tables where a sequential scan is faster anyway
Read EXPLAIN ANALYZE output, measure before and after, and remove indexes that are not being used.