Index the queries you run

How to do it

Index foreign key columns, because PostgreSQL and SQLite don't do it for you. MySQL's InnoDB does. Put equality columns first in a composite index and the range or sort column last. Each index slows every write. Drop one only after its usage statistics show zero scans over a full business cycle, such as a month. In PostgreSQL, build indexes on live tables with CREATE INDEX CONCURRENTLY, which cannot run inside a transaction.

Example

-- Serves: WHERE customer_id = $1 ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY orders_customer_created ON orders (customer_id, created_at DESC);
-- PostgreSQL: indexes never scanned since the statistics were reset
SELECT indexrelid::regclass FROM pg_stat_user_indexes s JOIN pg_index i USING (indexrelid)
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary;

How to check

Run the 20 most frequent queries with EXPLAIN on production-sized data. None does a full scan of a table with more than 10,000 rows. The unused index query returns no rows on statistics collected for at least a month.